﻿/*************************************************************************************
 *
 * 文 件 名:   CommonDbOperate
 * 描    述:   通用数据库交互类
 * 
 * 版    本：  V1.0
 * 创 建 者：  YSW 
 * 创建时间：  2021-10-18 10:19:55
 * ======================================
 * 历史更新记录
 * 版本：V          修改时间：         修改人：
 * 修改内容：
 * ======================================
*************************************************************************************/

using CommonModel.Model;
using CommonModel.Util;
using GXRTBTC.DataStorageService.Model;
using GXRTBTC.PublicClassLibrary.Field;
using GXRTBTC.PublicClassLibrary.Model;
using IoTSharp.Data.Taos;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.Linq;
using System.Text.RegularExpressions;
using static GXRTBTC.PublicClassLibrary.Model.AlarmColumns;

namespace GXRTBTC.PublicClassLibrary.Util
{
    /// <summary>
    /// dbConnection.GetType().Name三个数据库放回的字符串
    /// pgsql：NpgsqlConnection
    /// mysql：MySqlConnection
    /// dameng达梦：DmConnection
    /// 当前所有sql默认都是基于pgsql的
    /// </summary>
    public class DbUtil
    {
        #region 基础操作
        /// <summary>
        /// 动态参数替换
        /// 用于命名和指定参数的 SQL 语法使用 @ 符号，OLEDB 参数语法需要使用问号 (?) 作为参数占位符，而 Oracle 提供程序使用冒号 (:)
        /// pgsql使用(@)
        /// 达梦使用(:)
        /// OLEDB等其它未测试
        /// OleDbConnection特殊一些，替换成?
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static void FormatDbParameters(DbCommand dbCommand, string sql, object[] args)
        {
            if (args != null)
            { 
                //dynamic[] dbParameters = new dynamic[args.Length];
                DbParameter[] dbParameters = new DbParameter[args.Length];
                string dbTypeName = dbCommand.Connection.GetType().Name;
                string symbol = GetSymbol(dbTypeName);
                for (int i = 0; i < args.Length; i++)
                {
                    dbParameters[i] = dbCommand.CreateParameter();
                    dbParameters[i].ParameterName = symbol + i;
                    dbParameters[i].Value = args[i];

                    //参数{i}替换成相应数据@i或者:i
                    //这样写会被检测到有sql注入风险
                    //sql = sql.Replace("'{" + i + "}'", dbParameters[i].ParameterName).Replace("'{ " + i + "}'", dbParameters[i].ParameterName).Replace("'{" + i + " }'", dbParameters[i].ParameterName).Replace("'{ " + i + " }'", dbParameters[i].ParameterName)
                    //    .Replace("{" + i + "}", dbParameters[i].ParameterName).Replace("{ " + i + "}", dbParameters[i].ParameterName).Replace("{" + i + " }", dbParameters[i].ParameterName).Replace("{ " + i + " }", dbParameters[i].ParameterName);

                    //改为这样写
                    sql = Regex.Replace(sql, @"'\{" + i + @"\}'", dbParameters[i].ParameterName, RegexOptions.IgnoreCase);
                    sql = Regex.Replace(sql, @"'\{ " + i + @"\}'", dbParameters[i].ParameterName, RegexOptions.IgnoreCase);
                    sql = Regex.Replace(sql, @"'\{" + i + @" \}'", dbParameters[i].ParameterName, RegexOptions.IgnoreCase);
                    sql = Regex.Replace(sql, @"'\{ " + i + @"  \}'", dbParameters[i].ParameterName, RegexOptions.IgnoreCase);
                    sql = Regex.Replace(sql, @"\{" + i + @"\}", dbParameters[i].ParameterName, RegexOptions.IgnoreCase);
                    sql = Regex.Replace(sql, @"\{ " + i + @"\}", dbParameters[i].ParameterName, RegexOptions.IgnoreCase);
                    sql = Regex.Replace(sql, @"\{" + i + @" \}", dbParameters[i].ParameterName, RegexOptions.IgnoreCase);
                    sql = Regex.Replace(sql, @"\{ " + i + @" \}", dbParameters[i].ParameterName, RegexOptions.IgnoreCase);
                }
                dbCommand.Parameters.AddRange(dbParameters);
            }
            dbCommand.CommandText = sql;
        }

        /// <summary>
        /// 根据数据库类型，获取不同类型的参数前缀
        /// </summary>
        /// <param name="dbTypeName"></param>
        /// <returns></returns>
        public static string GetSymbol(string dbTypeName)
        {
            //大部分都是@
            string symbol = "@";
            //达梦
            //Oracle
            if ("DmConnection".Equals(dbTypeName) || dbTypeName.ToUpper().Contains("ORACLE"))
            {
                symbol = ":";
            }

            return symbol;
        }

        /// <summary>
        /// 执行sql
        /// 得到成功与否
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static bool ExecSql(DbConnection dbConnection, string sql, params object?[] args)
        {
            var sw = Stopwatch.StartNew();
            bool rs = false;
            sql = ReplaceIgnoreCase(dbConnection, sql);
            try
            {
                if (dbConnection.State != ConnectionState.Open)
                {
                    dbConnection.Open();
                }
                DbCommand dbCommand = dbConnection.CreateCommand();
                dbCommand.CommandTimeout = 7200;
                //dbCommand.CommandText = args == null ? sql : string.Format(sql, args);
                FormatDbParameters(dbCommand, sql, args);
                dbCommand.ExecuteNonQuery();
                rs = true;
            }
            catch (Exception e)
            {
                CommonUtil.mLogger.LogError("（ExecSql）执行sql【" + sql + "】失败！用时=" + sw.ElapsedMilliseconds + " " + e.Message);

                //达梦通信异常时
                if (e.Message.Contains("Communication  error"))
                {
                    try
                    {
                        dbConnection.Close();
                    }
                    catch { }
                    finally
                    {
                        rs = ExecSql(dbConnection, sql);
                    }
                }
            }
            return rs;
        }

        /// <summary>
        /// 执行sql
        /// 得到成功与否
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <param name="sql"></param>
        public static bool ExecSql(DbConnection dbConnection, string sql)
        {
            var sw = Stopwatch.StartNew();
            bool rs = false;
            sql = ReplaceIgnoreCase(dbConnection, sql);
            try
            {
                if (dbConnection.State != ConnectionState.Open)
                {
                    dbConnection.Open();
                }
                DbCommand dbCommand = dbConnection.CreateCommand();
                dbCommand.CommandTimeout = 7200;
                dbCommand.CommandText = sql;
                dbCommand.ExecuteNonQuery();
                rs = true;
            }
            catch (Exception e)
            {
                CommonUtil.mLogger.LogError("（ExecSql）执行sql【" + sql + "】失败！用时=" + sw.ElapsedMilliseconds + " " + e.Message);

                //达梦通信异常时
                if (e.Message.Contains("Communication  error"))
                {
                    try
                    {
                        dbConnection.Close();
                    }
                    catch { }
                    finally
                    {
                        rs = ExecSql(dbConnection, sql);
                    }
                }
            }
            return rs;
        }

        /// <summary>
        /// 达梦或者后面有其它数据库需要替换的，都写这里
        /// 替换掉pgsql的NULL值更改为空字符串的函数
        /// 忽略大小写的替换
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        private static string ReplaceIgnoreCase(DbConnection dbConnection, string sql)
        {
            string tmpSql = sql;
            if ("DmConnection".Equals(dbConnection.GetType().Name))
            {
                tmpSql = Regex.Replace(tmpSql, " true ", "1 ", RegexOptions.IgnoreCase);
                tmpSql = Regex.Replace(tmpSql, "=true", "=1", RegexOptions.IgnoreCase);
                tmpSql = Regex.Replace(tmpSql, "= true", "=1", RegexOptions.IgnoreCase);
                tmpSql = Regex.Replace(tmpSql, "=  true", "=1", RegexOptions.IgnoreCase);
                tmpSql = Regex.Replace(tmpSql, "=   true", "=1", RegexOptions.IgnoreCase);
                tmpSql = Regex.Replace(tmpSql, "='true'", "=1", RegexOptions.IgnoreCase);
                tmpSql = Regex.Replace(tmpSql, "= 'true'", "=1", RegexOptions.IgnoreCase);
                tmpSql = Regex.Replace(tmpSql, " false ", "0 ", RegexOptions.IgnoreCase);
                tmpSql = Regex.Replace(tmpSql, "=false", "=0", RegexOptions.IgnoreCase);
                tmpSql = Regex.Replace(tmpSql, "= false", "=0", RegexOptions.IgnoreCase);
                tmpSql = Regex.Replace(tmpSql, "=  false", "=0", RegexOptions.IgnoreCase);
                tmpSql = Regex.Replace(tmpSql, "=   false", "=0", RegexOptions.IgnoreCase);
                tmpSql = Regex.Replace(tmpSql, "='false'", "=0", RegexOptions.IgnoreCase);
                tmpSql = Regex.Replace(tmpSql, "= 'false'", "=0", RegexOptions.IgnoreCase);

                //tmpSql = Regex.Replace(tmpSql, ",true", "1", RegexOptions.IgnoreCase);
                //tmpSql = Regex.Replace(tmpSql, ", true", "1", RegexOptions.IgnoreCase);
                //tmpSql = Regex.Replace(tmpSql, ",false", "0", RegexOptions.IgnoreCase);
                //tmpSql = Regex.Replace(tmpSql, ", false", "0", RegexOptions.IgnoreCase);
            }

            return tmpSql;
        }

        /// <summary>
        /// 执行sql
        /// 得到影响条数
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <param name="sql"></param>
        public static int ExecSqlInt(DbConnection dbConnection, string sql)
        {
            var sw = Stopwatch.StartNew();
            sql = ReplaceIgnoreCase(dbConnection, sql);
            try
            {
                if (dbConnection.State != ConnectionState.Open)
                {
                    dbConnection.Open();
                }
                DbCommand dbCommand = dbConnection.CreateCommand();
                dbCommand.CommandTimeout = 7200;
                dbCommand.CommandText = sql;
                return dbCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                CommonUtil.mLogger.LogError("（ExecSqlInt）执行sql【" + sql + "】失败！用时=" + sw.ElapsedMilliseconds + " " + e.Message);

                int rs = 0;

                //达梦通信异常时
                if (e.Message.Contains("Communication  error"))
                {
                    try
                    {
                        dbConnection.Close();
                    }
                    catch { }
                    finally
                    {
                        rs = ExecSqlInt(dbConnection, sql);
                    }
                }
                return rs;
            }
        }

        /// <summary>
        /// 获取查询结果行数
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int GetResultColNum(DbConnection dbConnection, string sql)
        {
            List<Dictionary<string, string>> rs = ExecReader(dbConnection, sql);
            return rs == null ? 0 : rs.Count;
        }

        /// <summary>
        /// 执行sql，返回结果
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static List<Dictionary<string, string>> ExecReader(DbConnection dbConnection, string sql, bool showLog = true)
        {
            return ExecReader<Dictionary<string, string>>(dbConnection, sql, showLog);
        }

        /// <summary>
        /// 执行sql
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbConnection"></param>
        /// <param name="sql"></param>
        /// <param name="arg0"></param>
        /// <returns></returns>
        public static List<Dictionary<string, string>> ExecReader(DbConnection dbConnection, string sql, object? arg0)
        {
            return ExecReader<Dictionary<string, string>>(dbConnection, sql, new object[] { arg0 });
        }

        /// <summary>
        /// 执行sql
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbConnection"></param>
        /// <param name="sql"></param>
        /// <param name="arg0"></param>
        /// <param name="arg1"></param>
        /// <returns></returns>
        public static List<Dictionary<string, string>> ExecReader(DbConnection dbConnection, string sql, object? arg0, object? arg1)
        {
            return ExecReader<Dictionary<string, string>>(dbConnection, sql, new object[] { arg0, arg1 });
        }

        /// <summary>
        /// 执行sql
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbConnection"></param>
        /// <param name="sql"></param>
        /// <param name="arg0"></param>
        /// <param name="arg1"></param>
        /// <param name="arg2"></param>
        /// <returns></returns>
        public static List<Dictionary<string, string>> ExecReader(DbConnection dbConnection, string sql, object? arg0, object? arg1, object? arg2)
        {
            return ExecReader<Dictionary<string, string>>(dbConnection, sql, new object[] { arg0, arg1, arg2 });
        }

        //使用nullable、notnull或maybe注释来标记一个类型或成员是否可为null
        //#nullable enable

        /// <summary>
        /// 执行sql
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbConnection"></param>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static List<T> ExecReader<T>(DbConnection dbConnection, string sql, params object?[] args)
        {
            //var sw = Stopwatch.StartNew();
            List<T> lsRS = new();
            DbDataReader dataReader = null;
            sql = ReplaceIgnoreCase(dbConnection, sql);
            try
            {
                if (dbConnection.State != ConnectionState.Open)
                {
                    dbConnection.Open();
                }
                DbCommand dbCommand = dbConnection.CreateCommand();
                //dbCommand.CommandText = args == null ? sql : string.Format(sql, args);
                FormatDbParameters(dbCommand, sql, args);
                dataReader = dbCommand.ExecuteReader();
                //lsRS = dataReader.ToList<T>();
                lsRS = dataReader.ToJson().ToObject<List<T>>();
                dataReader.Close();
            }
            catch (Exception e)
            {
                //达梦通信异常时
                if (e.Message.Contains("Communication  error"))
                {
                    try
                    {
                        dbConnection.Close();
                    }
                    catch { }
                    finally
                    {
                        lsRS = ExecReader<T>(dbConnection, sql, args);
                    }
                }
            }
            finally
            {
                if (dataReader != null && !dataReader.IsClosed)
                {
                    dataReader.Close();
                }
            }
            return lsRS;
        }

        /// <summary>
        /// 执行sql
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbConnection"></param>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static List<Dictionary<string, string>> ExecReader(DbConnection dbConnection, string sql, params object?[] args)
        {
            return ExecReader<Dictionary<string, string>>(dbConnection, sql, args);
        }

        /// <summary>
        /// 执行sql，返回结果
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static List<T> ExecReader<T>(DbConnection dbConnection, string sql, bool showLog = true)
        {
            var sw = Stopwatch.StartNew();
            List<T> lsRS = new();
            DbDataReader dataReader = null;
            sql = ReplaceIgnoreCase(dbConnection, sql);
            try
            {
                if (dbConnection.State != ConnectionState.Open)
                {
                    dbConnection.Open();
                }
                DbCommand dbCommand = dbConnection.CreateCommand();
                if (showLog)
                {
                    dbCommand.CommandTimeout = 60000;
                }
                dbCommand.CommandText = sql;

                dataReader = dbCommand.ExecuteReader();
                //lsRS = dataReader.ToList<T>();
                lsRS = dataReader.ToJson().ToObject<List<T>>();
                dataReader.Close();
            }
            catch (Exception e)
            {
                long times = sw.ElapsedMilliseconds;
                if (showLog)
                {
                    CommonUtil.mLogger.LogError("（ExecReader）执行sql【" + sql + "】失败！用时=" + sw.ElapsedMilliseconds + " " + e.Message);
                }

                //达梦通信异常时
                if (e.Message.Contains("Communication  error"))
                {
                    try
                    {
                        dbConnection.Close();
                    }
                    catch { }
                    finally
                    {
                        lsRS = ExecReader<T>(dbConnection, sql, showLog);
                    }
                }
                else if (e.Message.Contains("请求执行超时") && times < 5000)
                {
                    try
                    {
                        dbConnection.Close();
                    }
                    catch { }
                    finally
                    {
                        lsRS = ExecReader<T>(dbConnection, sql, showLog);
                    }
                }
            }
            finally
            {
                if (dataReader != null && !dataReader.IsClosed)
                {
                    dataReader.Close();
                }
            }
            return lsRS;
        }

        /// <summary>
        /// 获取单个值
        /// 带参数
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static string GetExecValue(DbConnection dbConnection, string sql, params object?[] args)
        {
            string value = "";
            try
            {
                List<Dictionary<string, string>> rs = ExecReader(dbConnection, sql, args);
                if (rs.Count > 0)
                {
                    value = rs[0].Values.ToList()[0];
                }
            }
            catch (Exception e)
            {
                CommonUtil.mLogger.LogError("获取值失败！" + e.Message);
            }
            return value;
        }

        /// <summary>
        /// 获取单个值
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static string GetExecValue(DbConnection dbConnection, string sql)
        {
            string value = "";
            try
            {
                List<Dictionary<string, string>> rs = ExecReader(dbConnection, sql);
                if (rs.Count > 0)
                {
                    value = rs[0].Values.ToList()[0];
                }
            }
            catch (Exception e)
            {
                CommonUtil.mLogger.LogError("获取值失败！" + e.Message);
            }
            return value;
        }

        /// <summary>
        /// 获取单个统计
        /// 带参数
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static int GetExecInt(DbConnection dbConnection, string sql, params object?[] args)
        {
            string rs = GetExecValue(dbConnection, sql, args);
            return TransForm.IsInt(rs) ? int.Parse(rs) : -1;
        }

        /// <summary>
        /// 获取单个统计
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int GetExecInt(DbConnection dbConnection, string sql)
        {
            string rs = GetExecValue(dbConnection, sql);
            return TransForm.IsInt(rs) ? int.Parse(rs) : -1;
        }
        /// <summary>
        /// 获取结果行数
        /// 带参数
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static int GetExecColSum(DbConnection dbConnection, string sql, params object?[] args)
        {
            int colSum = 0;
            try
            {
                List<Dictionary<string, string>> rs = ExecReader(dbConnection, sql, args);
                colSum = rs.Count;
            }
            catch (Exception e)
            {
                CommonUtil.mLogger.LogError("获取值失败！" + e.Message);
            }
            return colSum;
        }

        /// <summary>
        /// 获取结果行数
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int GetExecColSum(DbConnection dbConnection, string sql)
        {
            int colSum = 0;
            try
            {
                List<Dictionary<string, string>> rs = ExecReader(dbConnection, sql);
                colSum = rs.Count;
            }
            catch (Exception e)
            {
                CommonUtil.mLogger.LogError("获取值失败！" + e.Message);
            }
            return colSum;
        }

        #endregion

        #region 其它

        /// <summary>
        /// 读取xml配置
        /// 从数据库读取
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<int, TableInfo> initSchemaMapFromDb(DbConnection dbConnection)
        {
            Dictionary<int, TableInfo> tableInfos = new();
            Dictionary<string, SchemaDocument> tmpDict = new();

            //从主表获取
            string sql = "SELECT id, file_name, schema_name, COALESCE(msg_type, '') msg_type, COALESCE(xml_type, '') xml_type, COALESCE(prefix_cols, '') prefix_cols FROM file_param_main where use_flag = 1 ORDER BY id";

            List<Dictionary<string, string>> lsDatas = ExecReader(dbConnection, sql);
            foreach (var data in lsDatas)
            {
                SchemaDocument schDoc = new()
                {
                    //name = dataReader.GetString(2),
                    //file_name = dataReader.GetString(1),
                    //msgType = dataReader.GetString(3),
                    //xmlType = dataReader.GetString(4),
                    //id = dataReader.GetValue(0).ToString(),
                    //prefixCols = dataReader.GetString(5),
                    name = data["schema_name"],
                    file_name = data["file_name"],
                    msgType = data["msg_type"],
                    xmlType = data["xml_type"],
                    id = data["id"],
                    prefixCols = data["prefix_cols"]
                };

                if (!tmpDict.ContainsKey(schDoc.file_name))
                {
                    tmpDict.Add(schDoc.file_name, schDoc);
                }
            }

            string tableName = "file_param_detail";
            //根据层级初始化数据 
            int layerNum = 3;
            sql = "SELECT MAX(layer) layer FROM " + tableName;
            lsDatas = ExecReader(dbConnection, sql);
            if (lsDatas.Count > 0)
            {
                layerNum = int.Parse(lsDatas[0]["layer"]);
            }

            //循环获取配置元素数据
            foreach (string key in tmpDict.Keys)
            {
                SchemaDocument schDoc = tmpDict[key];
                Dictionary<int, SchemaDictionary> layerPre = new();
                int maxLayer = 0;
                for (int i = 0; i <= layerNum; i++)
                {
                    layerPre.Add(i, new());
                }

                /*****************从明细表获取************************/
                //sql = "SELECT id,main_id,file_name,parent_id,child_num,idx,parent_idx,name,type,size,device_type,loop_val,code,isalarm,alarmvalue,alarmname,issave,get_time, memo,layer, loop_new" +
                sql = "SELECT id,main_id,parent_id,child_num,idx,parent_idx,name,type,size, COALESCE(device_type, '') device_type,COALESCE(loop_val, '') loop_val,COALESCE(code, '') code,issave,get_time,layer, loop_new" +
                    " FROM " + tableName + " a WHERE a.main_id = " + schDoc.id + " ORDER BY main_id, sort";
                lsDatas = ExecReader(dbConnection, sql);

                //动态字段
                Dictionary<string, string> dynamic = new();
                Dictionary<int, SchemaDictionary> schema = new();
                foreach (var tmp in lsDatas)
                {
                    Dictionary<string, string> data = tmp.ToDictionary(d => d.Key.ToLower(), d => d.Value);

                    //层级
                    //int layer = int.Parse(dataReader.GetValue(19).ToString());
                    int layer = int.Parse(data["layer"]);
                    maxLayer = (layer > maxLayer ? layer : maxLayer);
                    //int childNum = int.Parse(dataReader.GetValue(4).ToString());
                    int childNum = int.Parse(data["child_num"]);
                    string loop_val = data["loop_val"];
                    if (!"".Equals(loop_val) && !TransForm.IsInt(loop_val) && !dynamic.ContainsKey(loop_val))
                    {
                        //此处dynamic的value赋予默认值1
                        dynamic.Add(loop_val, "".Equals(data["loop_new"]) ? "1" : data["loop_new"]);
                    }

                    if (layer == 0)
                    {
                        GetElement(data, schema, new SchemaDictionary() { isNode = childNum > 0 });
                        layerPre[layer] = schema[schema.Count - 1];
                    }
                    else
                    {
                        if ((((SchemaDictionary)layerPre[layer - 1])).fieldDictionary == null)
                        {
                            ((SchemaDictionary)layerPre[layer - 1]).fieldDictionary = new();
                        }
                        GetElement(data, layerPre[layer - 1].fieldDictionary, new SchemaDictionary() { isNode = childNum > 0 });
                        layerPre[layer] = (((SchemaDictionary)layerPre[layer - 1])).fieldDictionary[(((SchemaDictionary)layerPre[layer - 1])).fieldDictionary.Count - 1];
                    }
                }
                schDoc.dynamicParaDictionary = dynamic;
                schDoc.fieldDictionary = schema;//将已读取XML文件后的XML字段规则放进schemaDocument
                /*****************从明细表获取************************/

                tableInfos.Add(int.Parse(schDoc.id), new TableInfo(schDoc) { layerNum = maxLayer });
            }
            return tableInfos;
        }

        /// <summary>
        /// 子元素获取
        /// </summary>
        /// <param name="dataReader"></param>
        /// <param name="schema"></param>
        /// <param name="field"></param>
        private static void GetElement(Dictionary<string, string> data, Dictionary<int, SchemaDictionary> schema, SchemaDictionary field)
        {
            //field.id = int.Parse(dataReader.GetValue(0).ToString());
            field.id = int.Parse(data["id"]);
            //field.name = dataReader.GetString(7);
            field.name = data["name"];
            //field.index = int.Parse(dataReader.GetValue(5).ToString());
            field.index = int.Parse(data["idx"]);
            //field.type = DataTypeUtils.getType(dataReader.GetString(8));
            field.type = DataTypeUtils.getType(data["type"]);
            //field.memo = dataReader.GetString(18);
            //field.memo = data["memo"];
            //field.size = int.Parse(dataReader.GetValue(9).ToString());
            field.size = int.Parse(data["size"]);
            //field.deviceType = dataReader.GetString(10);
            field.deviceType = data["device_type"];
            //field.isAlarm = "1".Equals(dataReader.GetValue(13).ToString());
            //field.isAlarm = "1".Equals(data["isalarm"]);
            //field.alarmName = dataReader.GetString(15);
            //field.alarmName = data["alarmname"];
            //field.alarmValue = dataReader.GetString(14);
            //field.alarmValue = data["alarmvalue"];
            //field.loop = dataReader.GetString(11);
            field.loop = data["loop_val"];
            //field.code = dataReader.GetString(12);
            field.code = data["code"];
            //如果是SchemaDictionary，即使数据库没有勾选保存，也默认保存，否则后面的取字段的逻辑可能会取不到数据
            //field.isSave = field.isNode ? "1" : dataReader.GetValue(16).ToString();
            field.isSave = field.isNode ? "1" : data["issave"];
            //field.layer = int.Parse(dataReader.GetValue(19).ToString());
            field.layer = int.Parse(data["layer"]);
            schema.Add(schema.Count, field);
        }

        /// <summary>
        /// 从资源发现表初始化数据，已发现未处理的不重新提示
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, PositionInfo> InitPositionInfoFromNew(DbConnection dbConnection, Dictionary<string, PositionInfo> positionInfos = null)
        {
            //ipv4和ipv6分别和设备类型组成要给key，对应同一个工位号position_id
            if (positionInfos == null)
            {
                positionInfos = new Dictionary<string, PositionInfo>();
            }
            try
            {
                List<Dictionary<string, string>> lsDatas = ExecReader(dbConnection, "select ip, ip_type, device_type from resource_search_new where is_sure = 0 order by id");
                foreach (Dictionary<string, string> data in lsDatas)
                {
                    //string ipType = dataReader.GetValue(1).ToString();
                    string ipType = data["ip_type"];
                    //string ip = dataReader.GetString(0);
                    string ip = data["ip"];
                    PositionInfo positionInfo = new()
                    {
                        ipv4 = "0".Equals(ipType) ? ip : "",
                        ipv6 = "0".Equals(ipType) ? "" : ip,
                        //deviceType = dataReader.GetString(2),
                        deviceType = data["device_type"],
                        Code = "",
                        stationCode = "",
                        IsAdd = false
                    };
                    positionInfos.TryAdd(ip + "_" + positionInfo.deviceType, positionInfo);
                }
            }
            catch (Exception e)
            {
                positionInfos = null;
                CommonUtil.mLogger.LogError("从resource_search_new初始化配置失败！" + e.Message);
                try
                {
                    if (e.Message.Contains("not open"))
                    {
                        dbConnection.Open();
                    }
                }
                catch { }
            }

            return positionInfos;
        }

        /// <summary>
        /// 批量添加数据到resource_search_new
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static bool AddPositionInfoToDb(DbConnection dbConnection, PositionInfo positionInfo)
        {
            bool rs = true;
            string sql = "";
            try
            {
                string insHead = "insert into resource_search_new(ip, ip_type, device_type, port)";
                lock (positionInfo)
                {
                    if (positionInfo.IsAdd)
                    {
                        string ip = "0".Equals(positionInfo.ipType) ? positionInfo.ipv4 : positionInfo.ipv6;
                        //插入数据
                        sql = insHead + " select '" + ip + "', '" + positionInfo.ipType + "', '" + positionInfo.deviceType + "'," + positionInfo.port.ToString() +
                            " where not exists(select 1 from resource_search_new b where b.ip = '" + ip + "' and b.device_type = '" + positionInfo.deviceType +
                            "' and b.port = " + positionInfo.port.ToString() + ")";
                        ExecSql(dbConnection, sql);
                        positionInfo.IsAdd = false;
                    }
                }
            }
            catch (Exception e)
            {
                rs = false;
                CommonUtil.mLogger.LogError("插入数据到resource_search_new失败！" + sql + " " + e.Message);
            }
            return rs;
        }

        /// <summary>
        /// 新结构
        /// 获取初始化基础配置数据 通信模型
        /// flag控制是否获取带位置索引的模型
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <param name="flag">0：获取模型（用于通信返回），1：获取包含位置ID的模型，用于和redis通信，获取值给返回的模型</param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, Dictionary<string, Object>>> GetStationBasicNodeModel(DbConnection dbConnection)
        {
            Dictionary<string, Dictionary<string, Dictionary<string, Object>>> nodeModelAll = new();
            Dictionary<string, Dictionary<string, Object>> nodeModelBasic = new();
            Dictionary<string, Dictionary<string, Object>> nodeModelIdx = new();
            //nodeModelBasic保存该位置的值
            nodeModelAll.TryAdd("nodeModelBasic", nodeModelBasic);
            //nodeModelIdx保存位置详细信息，用于辅助nodeModelBasic快速定位获取值
            nodeModelAll.TryAdd("nodeModelIdx", nodeModelIdx);
            try
            {
                //一次性全量
                //空节点数据
                //order by里的case，指定time_out字段位置，先于其他字段排在前面
                string sql = "select a.node_id, a.station_id, a.node_name, a.parent_node_id, COALESCE(b.layer, 0) layer, COALESCE(a.position_id, '') position_id, COALESCE(a.relation_position_id, '') relation_position_id, a.sub_system_id, a.device_type_id, " +
                    "	COALESCE(a.static_model_id, '') static_model_id, COALESCE(a.dynamic_model_id, '') dynamic_model_id, COALESCE(a.setitem_model_id, '') setitem_model_id, COALESCE(a.operate_model_id, '') operate_model_id, COALESCE(b.pathmain, '') pathmain" +
                    " from station_node a left join" +
                    " (" +
                    "   with recursive cte as " +
                    "   ( " +
                    "    select a.node_id, 1 layer, cast(a.station_id as VARCHAR(1000)) pathmain" +
                    "    from station_node a where a.parent_node_id = 0" +
                    "    union all " +
                    "    select b.node_id, cte.layer + 1 layer, cast(cte.pathmain||'&-&'||b.node_name as VARCHAR(1000)) pathmain" +
                    "    from cte, station_node b " +
                    "    where b.parent_node_id = cte.node_id " +
                    "   ) " +
                    "   SELECT node_id, layer, pathmain from cte" +
                    " ) b on a.parent_node_id = b.node_id " +
                    //" where a.station_id= '0801080400' " +
                    " order by a.station_id, a.parent_node_id, a.node_id";

                //达梦sql写法
                if ("DmConnection".Equals(dbConnection.GetType().Name))
                {
                    sql = " select a.node_id, a.station_id, a.node_name, a.parent_node_id, ISNULL(b.layer, 0) layer, ISNULL(a.position_id, '') position_id, ISNULL(a.relation_position_id, '') relation_position_id, a.sub_system_id, a.device_type_id, " +
                        "    ISNULL(a.static_model_id, '') static_model_id, ISNULL(a.dynamic_model_id, '') dynamic_model_id, ISNULL(a.setitem_model_id, '') setitem_model_id, ISNULL(a.operate_model_id, '') operate_model_id, ISNULL(b.pathmain, '') pathmain " +
                        " from station_node a left join(" +
                        "  select node_id, LENGTH(layer) layer, pathmain FROM (" +
                        "   SELECT node_id, SYS_CONNECT_BY_PATH(1, '') layer, SYS_CONNECT_BY_PATH(case parent_node_id when 0 then station_id else '&-&' || node_name end, '') pathmain" +
                        "   FROM station_node" +
                        "   where 1=1 " +
                        "    START WITH parent_node_id = 0" +
                        "    CONNECT BY PRIOR node_id = parent_node_id" +
                        "    ORDER SIBLINGS BY node_id)" +
                        " ) b on a.parent_node_id = b.node_id" +
                        //" where a.station_id = '0801080400'" +
                        " order by a.station_id, a.parent_node_id, a.node_id";
                }
                List<Dictionary<string, string>> lsDatas = ExecReader(dbConnection, sql);

                ////全量数据太多了，按分中心分组，此段还没测试过
                //List<Dictionary<string, string>> lsDatas = new List<Dictionary<string, string>>();
                //string sql = "select distinct subcenter_id from station";
                //List<Dictionary<string, string>> lsSubcenterIds = ExecReader(dbConnection, sql);
                //foreach (Dictionary<string, string> data in lsSubcenterIds)
                //{
                //    string subcenterId = data["subcenter_id"];

                //    sql = "select a.node_id, a.station_id, a.node_name, a.parent_node_id, COALESCE(b.layer, 0) layer, COALESCE(a.position_id, '') position_id, COALESCE(a.relation_position_id, '') relation_position_id, a.sub_system_id, a.device_type_id, " +
                //        "	COALESCE(a.static_model_id, '') static_model_id, COALESCE(a.dynamic_model_id, '') dynamic_model_id, COALESCE(a.setitem_model_id, '') setitem_model_id, COALESCE(a.operate_model_id, '') operate_model_id, COALESCE(b.pathmain, '') pathmain" +
                //        " from station_node a left join" +
                //        " (" +
                //        "   with recursive cte as " +
                //        "   ( " +
                //        "    select a.node_id, 1 layer, cast(a.station_id as VARCHAR(1000)) pathmain" +
                //        "    from station_node a, station S where a.parent_node_id = 0 and a.station_id = s.station_id and s.subcenter_id = '" + subcenterId + "'" +
                //        "    union all " +
                //        "    select b.node_id, cte.layer + 1 layer, cast(cte.pathmain||'&-&'||b.node_name as VARCHAR(1000)) pathmain" +
                //        "    from cte, station_node b, station S " +
                //        "    where b.parent_node_id = cte.node_id and b.station_id = s.station_id and s.subcenter_id = '" + subcenterId + "'" +
                //        "   ) " +
                //        "   SELECT node_id, layer, pathmain from cte" +
                //        " ) b on a.parent_node_id = b.node_id " +
                //        " order by a.station_id, a.parent_node_id, a.node_id";
                //    List<Dictionary<string, string>> tmpDatas = ExecReader(dbConnection, sql);
                //    lsDatas.Concat(tmpDatas);
                //}

                Dictionary<string, Object> tmpModelBasic = new();
                Dictionary<string, Object> tmpModelIdx = new();
                int layerPre = -1;
                string pathmainPre = "";
                List<string> layerNames;
                foreach (Dictionary<string, string> data in lsDatas)
                {
                    //台站ID
                    //string station_id = dataReader.GetValue("station_id").ToString();
                    string station_id = data["station_id"];
                    //层级
                    //int layer = int.Parse(dataReader.GetValue("layer").ToString());
                    int layer = int.Parse(data["layer"]);
                    //节点名称
                    //string node_name = (layer == 0 ? station_id : dataReader.GetValue("node_name").ToString());
                    string node_name = (layer == 0 ? station_id : data["node_name"]);

                    //工位号
                    //string positionId = dataReader.GetValue("position_id").ToString();
                    string positionId = data["position_id"];
                    //关联工位号
                    //string relation_position_id = dataReader.GetValue("relation_position_id").ToString();
                    string relation_position_id = data["relation_position_id"];

                    //节点路径
                    //string pathmain = dataReader.GetValue("pathmain").ToString();
                    string pathmain = data["pathmain"];
                    //子系统ID
                    //string sub_system_id = dataReader.GetValue("sub_system_id").ToString();
                    string sub_system_id = data["sub_system_id"];

                    //静态、动态、设置、操作关联ID
                    //string static_model_id = dataReader.GetValue("static_model_id").ToString();
                    //string dynamic_model_id = dataReader.GetValue("dynamic_model_id").ToString();
                    //string operate_model_id = dataReader.GetValue("operate_model_id").ToString();
                    //string setitem_model_id = dataReader.GetValue("setitem_model_id").ToString();
                    string static_model_id = data["static_model_id"];
                    string dynamic_model_id = data["dynamic_model_id"];
                    string operate_model_id = data["operate_model_id"];
                    string setitem_model_id = data["setitem_model_id"];

                    //是否有属性，没有属性的默认是空节点
                    bool hasProt = !"".Equals(static_model_id) || !"".Equals(dynamic_model_id) || !"".Equals(operate_model_id) || !"".Equals(setitem_model_id);

                    //第一层为台站
                    if (layer == 0)
                    {
                        if (!nodeModelBasic.ContainsKey(station_id))
                        {
                            nodeModelBasic.TryAdd(station_id, new Dictionary<string, Object>());
                        }
                        if (!nodeModelIdx.ContainsKey(station_id))
                        {
                            nodeModelIdx.TryAdd(station_id, new Dictionary<string, Object>());
                        }
                    }
                    else
                    {
                        //循环找到目标层次
                        layerNames = pathmain.Split("&-&").ToList();
                        if (layer != layerPre || !pathmain.Equals(pathmainPre))
                        {
                            tmpModelBasic = (Dictionary<string, Object>)nodeModelBasic[layerNames[0]];
                            tmpModelIdx = (Dictionary<string, Object>)nodeModelIdx[layerNames[0]];
                            for (int i = 1; i < layer; i++)
                            {
                                string tmpNode = layerNames[i];
                                if (tmpModelBasic.ContainsKey(tmpNode))
                                {
                                    tmpModelBasic = (Dictionary<string, Object>)tmpModelBasic[tmpNode];
                                }
                                if (tmpModelIdx.ContainsKey(tmpNode))
                                {
                                    tmpModelIdx = (Dictionary<string, Object>)tmpModelIdx[tmpNode];
                                }
                            }
                        }

                        tmpModelBasic.TryAdd(node_name, new Dictionary<string, Object>());
                        tmpModelIdx.TryAdd(node_name, new Dictionary<string, Object>());

                        //包含属性
                        if (hasProt)
                        {
                            //tmpModelIdx的值构造
                            Dictionary<string, Object> modelIdx = new();
                            modelIdx.TryAdd("positionId", positionId);  //工位号
                            modelIdx.TryAdd("rPositionId", relation_position_id);  //关联工位号
                            modelIdx.TryAdd("static_model_id", static_model_id);
                            modelIdx.TryAdd("dynamic_model_id", dynamic_model_id);
                            modelIdx.TryAdd("operate_model_id", operate_model_id);
                            modelIdx.TryAdd("setitem_model_id", setitem_model_id);
                            modelIdx.TryAdd("subSysId", sub_system_id);    //关联位置索引
                            ((Dictionary<string, Object>)tmpModelIdx[node_name]).TryAdd("rConfig", modelIdx);
                        }
                    }
                    layerPre = layer;
                    pathmainPre = pathmain;
                }
            }
            catch (Exception e)
            {
                nodeModelAll = null;
                CommonUtil.mLogger.LogError("从station_node获取模型数据失败！" + e);
                try
                {
                    if (e.Message.Contains("not open"))
                    {
                        dbConnection.Open();
                    }
                }
                catch { }
            }
            return nodeModelAll;
        }

        /// <summary>
        /// 新结构
        /// 返回模板信息，总览用
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, Dictionary<string, Dictionary<string, string>>>> GetNodeModel(DbConnection dbConnection)
        {
            Dictionary<string, Dictionary<string, Dictionary<string, Dictionary<string, string>>>> nodeModel = new();
            nodeModel.TryAdd("dynamic_model", new());
            nodeModel.TryAdd("static_model", new());
            nodeModel.TryAdd("operations_model", new());
            nodeModel.TryAdd("setitem_model", new());

            try
            {
                //order by里的case，指定time_out字段位置，先于其他字段排在前面
                string sql = "select model_id, para_name, COALESCE(upper(TRIM(parno)), '') parno, is_use_timetable, COALESCE(upper(TRIM(relation_parno)), '') relation_parno, a.sub_para_name, a.rule_symbol,a.is_fold " +
                    " from dynamic_model a where is_open = 1 " +
                    " order by model_id, case upper(para_name) when 'TIME_OUT' then 1 when 'IN_BROADCAST_TIME' then 3 else 2 end, para_id";
                List<Dictionary<string, string>> lsDetail = ExecReader(dbConnection, sql);
                if (lsDetail != null)
                {
                    foreach (var dict in lsDetail)
                    {
                        string model_id = dict["model_id"];
                        string para_name = dict["para_name"];
                        if (!nodeModel["dynamic_model"].ContainsKey(model_id))
                        {
                            nodeModel["dynamic_model"].TryAdd(model_id, new());
                        }
                        nodeModel["dynamic_model"][model_id].TryAdd(para_name, dict);
                    }
                }

                sql = "select model_id, para_name ,COALESCE(upper(parno), '') parno, COALESCE(upper(relation_parno), '') relation_parno, is_use_timetable, para_describe, template_id, template_paras, COALESCE(t.cmd_text, '') cmd_text, COALESCE(t.dft_paras, '') dft_paras, COALESCE(a.source_code, '') source_code " +
                    " from operations_model a  left join cmd_template t on template_id = t.id where is_open = 1" +
                    " order by model_id, para_id";
                lsDetail = ExecReader(dbConnection, sql);
                if (lsDetail != null)
                {
                    foreach (var dict in lsDetail)
                    {
                        string model_id = dict["model_id"];
                        string para_name = dict["para_name"];
                        if (!nodeModel["operations_model"].ContainsKey(model_id))
                        {
                            nodeModel["operations_model"].TryAdd(model_id, new());
                        }
                        nodeModel["operations_model"][model_id].TryAdd(para_name.ToUpper(), dict);
                    }
                }

                sql = "select model_id, para_name ,COALESCE(upper(parno), '') parno, para_type, para_length, para_code, COALESCE(upper(relation_parno), '') relation_parno, is_use_timetable, para_describe, remarks, " +
                    "COALESCE(parno_prefix, '') parno_prefix, COALESCE(refer_flag) refer_flag, COALESCE(loop_para_name) loop_para_name" +
                    " from setitem_model a order by model_id, para_id ";
                lsDetail = ExecReader(dbConnection, sql);
                if (lsDetail != null)
                {
                    foreach (var dict in lsDetail)
                    {
                        string model_id = dict["model_id"];
                        string para_name = dict["para_name"];
                        if (!nodeModel["setitem_model"].ContainsKey(model_id))
                        {
                            nodeModel["setitem_model"].TryAdd(model_id, new());
                        }
                        nodeModel["setitem_model"][model_id].TryAdd(para_name.ToUpper(), dict);
                    }
                }

                sql = "select model_id, para_name, para_value, 1 is_use_timetable " +
                    " from static_model a order by model_id, para_id";
                lsDetail = ExecReader(dbConnection, sql);
                if (lsDetail != null)
                {
                    foreach (var dict in lsDetail)
                    {
                        string model_id = dict["model_id"];
                        string para_name = dict["para_name"];
                        if (!nodeModel["static_model"].ContainsKey(model_id))
                        {
                            nodeModel["static_model"].TryAdd(model_id, new());
                        }
                        nodeModel["static_model"][model_id].TryAdd(para_name, dict);
                    }
                }
            }
            catch (Exception e)
            {
                nodeModel = null;
                CommonUtil.mLogger.LogError("从属性表获取获取模型数据失败！" + e.Message);
                try
                {
                    if (e.Message.Contains("not open"))
                    {
                        dbConnection.Open();
                    }
                }
                catch { }
            }

            return nodeModel;
        }

        /// <summary>
        /// 新结构
        /// 返回模板信息，告警用
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, Dictionary<string, string>>> GetAlarmModel(DbConnection dbConnection)
        {
            Dictionary<string, Dictionary<string, Dictionary<string, string>>> alarmModel = new();
            try
            {
                string sql = "select * from ( " +
                    "select model_id, para_name, para_value, '' parno, '' relation_parno, 0 is_open, 1 is_use_timetable, '-1' h0, '-1' l0, -1 alarm_id, -1 alarm_type, -1 alarm_level, '' alarm_title, " +
                    " 0 alarm_title_id, '' alarm_describe, -1 sub_system_id, '' sub_system_name, -1 device_type_id, '' device_type_name, " +
                    " '' sub_para_name, '' rule_symbol, '-1' is_fold, '0' alarm_show_delay, '0' alarm_disappear_delay, '0' alarm_always_show, " +
                    " 0 one_rules,0 two_rules,0 three_rules,0 four_rules,0 five_rules " +
                    " from static_model a where 1=1 and lower(para_name) in ('frequency','program_name','is_enable') " +
                    " union all " +
                    " select model_id, para_name, '' para_value, upper(TRIM(COALESCE(parno, ''))) parno, upper(TRIM(COALESCE(relation_parno, ''))) relation_parno, is_open, is_use_timetable, h0, l0, a.alarm_id, COALESCE(b.alarm_type, -1) alarm_type, b.alarm_level, c.alarm_title, c.alarm_title_id,COALESCE(b.alarm_describe, '') alarm_describe,  " +
                    " COALESCE(b.sub_system_id, -1) sub_system_id, COALESCE(f.dictionary_name, '') sub_system_name, COALESCE(b.device_type_id, -1) device_type_id, COALESCE(l.device_type_name, '') device_type_name, " +
                    " a.sub_para_name, a.rule_symbol,a.is_fold, b.alarm_show_delay, b.alarm_disappear_delay, b.alarm_always_show, " +
                    " b.one_rules,b.two_rules,b.three_rules,b.four_rules,b.five_rules" +
                    " from dynamic_model a left join alarm_basic_info b on a.alarm_id = b.alarm_id " +
                    " left join alarm_title_table c on b.alarm_title_id = c.alarm_title_id  " +
                    " left join device_type_info l on b.device_type_id = l.device_type_id " +
                    " left join dictionary_info f on b.sub_system_id = f.dictionary_value and f.dictionary_type = '1' and f.dictionary_english_name = 'sub_system' " +
                    " where (a.alarm_id <> -1 or (a.alarm_id = -1 and (lower(a.para_name) like '%program_name' or lower(a.para_name) like '%stream_name' or lower(a.para_name) like '%time' or lower(a.para_name) in ('frequency', 'program_name', 'is_enable') ))) " +
                    " ) a " +
                    " order by model_id, case upper(a.para_name) when 'TIME_OUT' then 1 when 'IN_BROADCAST_TIME' then 3 else case is_fold when '1' then 4 else 2 end end";
                List<Dictionary<string, string>> lsDetail = ExecReader(dbConnection, sql);
                foreach (var dict in lsDetail)
                {
                    string model_id = dict["model_id"];
                    string para_name = dict["para_name"];
                    if (!alarmModel.ContainsKey(model_id))
                    {
                        alarmModel.TryAdd(model_id, new());
                    }
                    alarmModel[model_id].TryAdd(para_name, dict);
                }
            }
            catch (Exception e)
            {
                alarmModel = null;
                CommonUtil.mLogger.LogError("从属性表获取告警模板数据失败！" + e.Message);
                try
                {
                    if (e.Message.Contains("not open"))
                    {
                        dbConnection.Open();
                    }
                }
                catch { }
            }

            return alarmModel;
        }

        /// <summary>
        /// 获取台站中应为匹配信息
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, string>> GetStationSubSystemMatch(DbConnection dbConnection)
        {
            Dictionary<string, Dictionary<string, string>> stationSubSystemMatch = new();
            try
            {
                //空节点数据
                string sql = "select a.station_id, a.node_name, n.dictionary_name, n.dictionary_value from station_node a, dictionary_info n " +
                    " where a.sub_system_id = n.dictionary_value and n.dictionary_type = '1' and n.dictionary_english_name = 'sub_system'  " +
                    " and exists(select 1 from station_node b where b.parent_node_id = 0 and b.node_id = a.parent_node_id) order by a.station_id, a.node_id";

                Dictionary<string, Object> tmpModel = new();
                List<Dictionary<string, string>> lsNodes = ExecReader(dbConnection, sql);
                foreach (Dictionary<string, string> node in lsNodes)
                {
                    //台站ID
                    string station_id = node["station_id"];
                    //英文名
                    string node_name = node["node_name"];
                    //中文名
                    string dictionary_name = node["dictionary_name"];
                    //子系统编号
                    string dictionary_value = node["dictionary_value"];

                    if (!stationSubSystemMatch.ContainsKey(station_id))
                    {
                        stationSubSystemMatch.TryAdd(station_id, new Dictionary<string, string>());
                        stationSubSystemMatch.TryAdd(station_id + "_en", new Dictionary<string, string>());
                        stationSubSystemMatch.TryAdd(station_id + "_id", new Dictionary<string, string>());
                    }
                    stationSubSystemMatch[station_id + "_en"].TryAdd(node_name, dictionary_name);   //编码匹配中文名
                    stationSubSystemMatch[station_id].TryAdd(dictionary_name, node_name);   //中文名匹配编码
                    stationSubSystemMatch[station_id + "_id"].TryAdd(node_name, dictionary_value);  //编码匹配子系统编号
                }
            }
            catch (Exception e)
            {
                stationSubSystemMatch = null;
                CommonUtil.mLogger.LogError("从node获取模型数据失败！" + e.Message);
                try
                {
                    if (e.Message.Contains("not open"))
                    {
                        dbConnection.Open();
                    }
                }
                catch { }
            }
            return stationSubSystemMatch;
        }

        /// <summary>
        /// 获取所有台站信息
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, Dictionary<string, string>>> GetAllStation(DbConnection dbConnection)
        {
            Dictionary<string, Dictionary<string, Dictionary<string, string>>> stations = new();
            try
            {
                //所有台站
                //string sql = "select * from station a";
                string sql = "select a.id,a.station_id,station_name,abbrev,subcenter_id, c.dept_name subcenter_name,a.dept_id,d.dept_name, COALESCE(region_name, '') region_name,COALESCE(city_name, '') city_name,COALESCE(country_name, '') country_name,COALESCE(village_name, '') village_name," +
                    " location,station_type,station_level,is_mv_station,measured_state," +
                    " longitude,latitude,construction_time,show_flag,show_date,is_monitor,duty_phone,road_condition,remarks,COALESCE(b.ip_addr,'') ip_addr, COALESCE(b.ip_type,0) ip_type" +
                    " from station a left join station_addr b on a.station_id = b.station_id " +
                    "  left join dept c on a.subcenter_id = c.dept_id " +
                    "  left join dept d on a.dept_id = d.dept_id " +
                    " order by station_level,station_id";
                List<Dictionary<string, string>> lsStations = ExecReader(dbConnection, sql);
                if (lsStations.Count > 0)
                {
                    stations.TryAdd("stationIds", new Dictionary<string, Dictionary<string, string>>());
                    stations.TryAdd("stationAddrs", new Dictionary<string, Dictionary<string, string>>());
                    stations.TryAdd("stationNames", new Dictionary<string, Dictionary<string, string>>());
                    foreach (Dictionary<string, string> station in lsStations)
                    {
                        stations["stationIds"].TryAdd(station["station_id"], station);
                        if (station.ContainsKey("ip_addr") && !"".Equals(station["ip_addr"]))
                        {
                            stations["stationAddrs"].TryAdd(station["ip_addr"], station);
                        }
                        if (station.ContainsKey("station_name") && !"".Equals(station["station_name"]))
                        {
                            stations["stationNames"].TryAdd(station["station_name"], station);
                        }
                    }
                }
            }
            catch (Exception e)
            {
                stations = null;
                CommonUtil.mLogger.LogError("从station获取数据失败！" + e.Message);
            }
            return stations;
        }

        /// <summary>
        /// 六路调谐器IP+端口，匹配都台站ID
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, Dictionary<string, string>>> GetSixTunerMatchInfo(DbConnection dbConnection)
        {
            Dictionary<string, Dictionary<string, Dictionary<string, string>>> tunerMatchInfo = new();
            try
            {
                //string sql = "select distinct station_id, video_ip, tuner_port from receive_video_info a order by video_ip, tuner_port, station_id";
                //20230112 ysw 新增num排序，作为工位号编码
                string sql = "select video_ip, tuner_port, station_id, ROW_NUMBER() over(partition by station_id ORDER bY video_ip, tuner_port, station_id) AS num " +
                    " from(select distinct video_ip, tuner_port, station_id" +
                    "  from receive_video_info a order by video_ip, tuner_port, station_id) b" +
                    " order by video_ip, tuner_port, station_id";
                List<Dictionary<string, string>> lsDetails = ExecReader(dbConnection, sql);
                foreach (Dictionary<string, string> detail in lsDetails)
                {
                    string video_ip = detail["video_ip"];
                    string tuner_port = detail["tuner_port"];
                    string station_id = detail["station_id"];
                    string num = detail["num"];
                    if (!tunerMatchInfo.ContainsKey(video_ip))
                    {
                        tunerMatchInfo.TryAdd(video_ip, new());
                    }
                    if (!tunerMatchInfo[video_ip].ContainsKey(tuner_port))
                    {
                        tunerMatchInfo[video_ip].TryAdd(tuner_port, new() { { "station_id", station_id }, { "num", num }, { "device_type", "0x0901" } });
                    }
                }
            }
            catch (Exception e)
            {
                tunerMatchInfo = null;
                CommonUtil.mLogger.LogError("从receive_video_info获取数据失败！" + e.Message);
            }
            return tunerMatchInfo;
        }

        /// <summary>
        /// dtmb远场IP-端口匹配
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, Dictionary<string, Dictionary<string, string>>>> GetDtmbMatchInfo(DbConnection dbConnection, out Dictionary<string, string> stationId2DtmbIp)
        {
            stationId2DtmbIp = new();
            Dictionary<string, Dictionary<string, Dictionary<string, Dictionary<string, string>>>> dtmbMatchInfo = new();
            try
            {
                string sql = "SELECT distinct a.dtmb_ip, a.dtmb_port, a.dtmb_dev, a.station_ip, a.station_id, '2' num, '0x0902' device_type FROM station_dtmb_info a where a.dtmb_port not in(2070, 2170)";
                List<Dictionary<string, string>> lsDetails = ExecReader(dbConnection, sql);
                foreach (Dictionary<string, string> detail in lsDetails)
                {
                    string dtmb_ip = detail["dtmb_ip"];
                    string dtmb_port = detail["dtmb_port"];
                    string dtmb_dev = detail["dtmb_dev"];
                    string stationId = detail["station_id"];
                    if (!dtmbMatchInfo.ContainsKey(dtmb_ip))
                    {
                        dtmbMatchInfo.TryAdd(dtmb_ip, new());
                        dtmbMatchInfo[dtmb_ip].TryAdd("port", new());
                        dtmbMatchInfo[dtmb_ip].TryAdd("addr", new());
                    }
                    if (!dtmbMatchInfo[dtmb_ip]["port"].ContainsKey(dtmb_port))
                    {
                        dtmbMatchInfo[dtmb_ip]["port"].TryAdd(dtmb_port, detail);
                    }
                    if (!dtmbMatchInfo[dtmb_ip]["addr"].ContainsKey(dtmb_dev))
                    {
                        dtmbMatchInfo[dtmb_ip]["addr"].TryAdd(dtmb_dev, detail);
                    }

                    if (!stationId2DtmbIp.ContainsKey(stationId))
                    {
                        stationId2DtmbIp.TryAdd(stationId, dtmb_ip);
                    }
                }
            }
            catch (Exception e)
            {
                dtmbMatchInfo = null;
                CommonUtil.mLogger.LogError("从receive_video_info获取数据失败！" + e.Message);
            }
            return dtmbMatchInfo;
        }

        /// <summary>
        /// 获取工位号编号
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, Dictionary<string, Dictionary<string, string>>>> GetPositionInfoMatchNo(DbConnection dbConnection)
        {
            Dictionary<string, Dictionary<string, Dictionary<string, Dictionary<string, string>>>> matchInfo = new()
            {
                { "port", new Dictionary<string, Dictionary<string, Dictionary<string, string>>>()},
                { "devAddr", new Dictionary<string, Dictionary<string, Dictionary<string, string>>>()}
            };
            try
            {
                //所有匹配关系
                string sql = "select COALESCE(dev_addr, '') dev_addr, dev_port, station_level, work_no, COALESCE(dev_type, '') dev_type from position_id_match where used = 1 order by id";
                List<Dictionary<string, string>> lsMatch = ExecReader(dbConnection, sql);
                //按台站级别分组
                foreach (Dictionary<string, string> match in lsMatch)
                {
                    string station_level = match["station_level"];
                    string port = match["dev_port"];
                    string devAddr = match["dev_addr"];
                    if (matchInfo["port"].ContainsKey(station_level))
                    {
                        matchInfo["port"][station_level].TryAdd(port, match);
                    }
                    else
                    {
                        matchInfo["port"].TryAdd(station_level, new Dictionary<string, Dictionary<string, string>>() { { port, match } });
                    }

                    if (matchInfo["devAddr"].ContainsKey(station_level))
                    {
                        matchInfo["devAddr"][station_level].TryAdd(devAddr, match);
                    }
                    else
                    {
                        matchInfo["devAddr"].TryAdd(station_level, new Dictionary<string, Dictionary<string, string>>() { { devAddr, match } });
                    }
                }
            }
            catch (Exception e)
            {
                matchInfo = null;
                CommonUtil.mLogger.LogError("从position_id_match获取数据失败！" + e.Message);
            }
            return matchInfo;
        }

        /// <summary>
        /// 从position_id_match获取工位号最大只为1的数据
        /// 方便后续解析遇到设备地址和端口都异常时，可以给与默认工位号，并通过解析
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, string>> GetPositionInfoMatchNoSpec(DbConnection dbConnection)
        {
            Dictionary<string, Dictionary<string, string>> matchInfo = new();
            try
            {
                //所有匹配关系
                string sql = "select distinct station_level, work_no, COALESCE(dev_type, '') dev_type, dev_port, COALESCE(dev_addr, '') dev_addr from position_id_match a " +
                    " where work_no = '1' and used = 1 and dev_type is not null " +
                    " and exists(select 1 from position_id_match d where a.dev_type = d.dev_type and d.dev_type is not null group by d.dev_type having max(d.work_no) = 1) " +
                    " order by station_level, dev_type";
                List<Dictionary<string, string>> lsMatch = ExecReader(dbConnection, sql);
                //按台站级别分组
                foreach (Dictionary<string, string> match in lsMatch)
                {
                    string station_level = match["station_level"];
                    string[] devtypes = match["dev_type"].Split(',');
                    string dev_port = match["dev_port"];
                    string dev_addr = match["dev_addr"];
                    if (!matchInfo.ContainsKey(station_level))
                    {
                        matchInfo.TryAdd(station_level, new());
                    }
                    foreach (string dev_type in devtypes)
                    { 
                        matchInfo[station_level].TryAdd(dev_type, dev_port + "," + dev_addr);
                    }
                }
            }
            catch (Exception e)
            {
                matchInfo = null;
                CommonUtil.mLogger.LogError("从position_id_match获取匹配数据失败！" + e.Message);
            }
            return matchInfo;
        }

        /// <summary>
        /// 从工位号反向匹配端口和设备地址
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, string>> GetPositionNoToPort(DbConnection dbConnection)
        {
            Dictionary<string, Dictionary<string, string>> positionNoMatchInfo = new();
            try
            {
                //所有匹配关系
                //string sql = "select a.station_level, COALESCE(a.dev_addr, '') dev_addr,COALESCE(a.dev_port, '') dev_port, COALESCE(a.dev_type||'_'||a.work_no, '') devno from position_id_match a where used = 1 order by station_level";
                string sql = "select a.station_level, COALESCE(a.dev_addr, '') dev_addr,COALESCE(a.dev_port, '') dev_port, COALESCE(a.dev_type, '') dev_type, COALESCE(a.work_no, '') work_no from position_id_match a where used = 1 order by station_level";
                List<Dictionary<string, string>> lsMatch = ExecReader(dbConnection, sql);
                //按台站级别分组
                foreach (Dictionary<string, string> match in lsMatch)
                {
                    string station_level = match["station_level"];
                    //string devno = match["devno"];
                    string dev_port = match["dev_port"];
                    string dev_addr = match["dev_addr"];
                    string work_no = match["work_no"];
                    string[] devtypes = match["dev_type"].Split(',');
                    if (!positionNoMatchInfo.ContainsKey(station_level))
                    {
                        positionNoMatchInfo.TryAdd(station_level, new());
                    }
                    //positionNoMatchInfo[station_level].TryAdd(devno, dev_port + "," + dev_addr);
                    foreach (string dev_type in devtypes)
                    {
                        string devno = dev_type + "_" + work_no;
                        positionNoMatchInfo[station_level].TryAdd(devno, dev_port + "," + dev_addr);
                    }
                }
            }
            catch (Exception e)
            {
                positionNoMatchInfo = null;
                CommonUtil.mLogger.LogError("从position_id_match获取匹配数据失败！" + e.Message);
            }
            return positionNoMatchInfo;
        }

        /// <summary>
        /// 新模型
        /// 获取操作数据
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, List<Dictionary<string, string>>> GetOperateAndSetModelInfo(DbConnection dbConnection)
        {
            Dictionary<string, List<Dictionary<string, string>>> operateInfo = new();
            try
            {
                //所有参数
                string sql = " select a.station_id, a.position_id, COALESCE(a.relation_position_id, '') relation_position_id, COALESCE(a.operate_model_id, '') operate_model_id, COALESCE(a.setitem_model_id, '') setitem_model_id" +
                    " from station_node a" +
                    " where a.operate_model_id !='' or a.setitem_model_id != ''";
                List<Dictionary<string, string>> lsMatch = ExecReader(dbConnection, sql);
                //position_id+node_name为key
                foreach (Dictionary<string, string> match in lsMatch)
                {
                    string positionId = match["position_id"];
                    if (!operateInfo.ContainsKey(positionId))
                    {
                        operateInfo.TryAdd(positionId, new());
                    }
                    operateInfo[positionId].Add(match);

                    //string rPositionId = match["relation_position_id"];
                    //string realPid = (!"".Equals(rPositionId)) ? rPositionId : positionId;
                    //if (!operateInfo.ContainsKey(realPid))
                    //{
                    //    operateInfo.TryAdd(realPid, new());
                    //}
                    //operateInfo[realPid].Add(match);
                }
            }
            catch (Exception e)
            {
                operateInfo = null;
                CommonUtil.mLogger.LogError("从operations获取数据失败！" + e.Message);
            }
            return operateInfo;
        }

        /// <summary>
        /// 新结构
        /// 从动态表获取告警信息
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, Dictionary<string, Dictionary<string, Dictionary<string, string>>>>> GetAlarmModelInfo(DbConnection dbConnection)
        {
            Dictionary<string, Dictionary<string, Dictionary<string, Dictionary<string, Dictionary<string, string>>>>> realAlarmInfo = new();
            try
            {
                string sql = " select a.node_id, a.station_id, a.node_name, r.node_name parent_node_name, COALESCE(a.position_id, '') position_id, COALESCE(a.relation_position_id, '') relation_position_id,  " +
                    " a.sub_system_id, COALESCE(f.dictionary_name, '') sub_system_name, a.device_type_id, COALESCE(l.device_type_name,'') device_type_name,  " +
                    " COALESCE(a.static_model_id, '') static_model_id, COALESCE(a.dynamic_model_id, '') dynamic_model_id " +
                    " from station_node a " +
                    " left join device_type_info l on a.device_type_id = l.device_type_id " +
                    " left join dictionary_info f on a.sub_system_id = f.dictionary_value and f.dictionary_type = '1' and f.dictionary_english_name = 'sub_system' " +
                    " , station_node r " +
                    " where a.parent_node_id = r.node_id and a.position_id != '' and a.position_id is not null ";
                List<Dictionary<string, string>> lsAlarm = ExecReader(dbConnection, sql);

                //以台站分组
                foreach (Dictionary<string, string> alarm in lsAlarm)
                {
                    string station_id = alarm["station_id"];
                    //工位号
                    string position_id = alarm["position_id"];
                    //再次分层
                    //string device = alarm["parent_node_name"].ToUpper() + "-" + position_id.ToUpper();
                    string device = alarm["node_name"].ToUpper() + "-" + position_id.ToUpper();
                    //台站下一级，为每个告警定义一个唯一的key
                    //工位号+节点父级名称+节点名称
                    //string alarm_key = device + "-" + alarm["node_name"].ToUpper();

                    if (!realAlarmInfo.ContainsKey(station_id))
                    {
                        realAlarmInfo.TryAdd(station_id, new Dictionary<string, Dictionary<string, Dictionary<string, Dictionary<string, string>>>>());
                    }

                    if (!realAlarmInfo[station_id].ContainsKey(position_id))
                    {
                        realAlarmInfo[station_id].TryAdd(position_id, new Dictionary<string, Dictionary<string, Dictionary<string, string>>>());
                    }

                    if (!realAlarmInfo[station_id][position_id].ContainsKey(device))
                    {
                        realAlarmInfo[station_id][position_id].TryAdd(device, new Dictionary<string, Dictionary<string, string>>());
                    }

                    if (!realAlarmInfo[station_id][position_id][device].ContainsKey("rConfig"))
                    {
                        realAlarmInfo[station_id][position_id][device].TryAdd("rConfig", alarm);
                    }
                }
            }
            catch (Exception e)
            {
                realAlarmInfo = null;
                CommonUtil.mLogger.LogError("从station_node获取数据失败！" + e.Message);
            }
            return realAlarmInfo;
        }

        /// <summary>
        /// 获取台站频率匹配节目信息关系信息
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, Dictionary<string, string>>> GetFrqAndProgram(DbConnection dbConnection)
        {
            Dictionary<string, Dictionary<string, Dictionary<string, string>>> frqAndProgram = new();
            try
            {
                //所有参数
                string sql = "select distinct a.station_id, a.frequency, a.program_id, b.program_name, b.summary, b.program_level,a.program_type,a.order_num, COALESCE(a.offmacrepairtime, 0) offMacRepairTime " +
                    " from station_program_rel a, program_info b " +
                    " where a.program_id = b.program_id " +
                    " order by a.station_id, a.program_type, a.order_num, b.program_level, a.program_id";
                List<Dictionary<string, string>> lsData = ExecReader<Dictionary<string, string>>(dbConnection, sql);
                //以台站分组
                foreach (Dictionary<string, string> data in lsData)
                {
                    //台站
                    string station_id = data.ContainsKey("station_id") ? data["station_id"].ToString() : "";
                    //频率
                    string frequency = data["frequency"].ToString();
                    if (!frqAndProgram.ContainsKey(station_id))
                    {
                        frqAndProgram.TryAdd(station_id, new Dictionary<string, Dictionary<string, string>>());
                    }

                    if (!frqAndProgram[station_id].ContainsKey(frequency))
                    {
                        frqAndProgram[station_id].TryAdd(frequency, data);
                    }
                }
            }
            catch (Exception e)
            {
                frqAndProgram = null;
                CommonUtil.mLogger.LogError("从station_program_rel，program_info获取数据失败！" + e.Message);
            }
            return frqAndProgram;
        }

        /// <summary>
        /// 数字电视节目明细信息
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, List<Dictionary<string, string>>>> GetDtvProgram(DbConnection dbConnection)
        {
            Dictionary<string, Dictionary<string, List<Dictionary<string, string>>>> dtvProgram = new();
            try
            {
                //所有参数
                string sql = "select a.station_id, a.frequency, a.program_id, COALESCE(l.sid, '') sid, COALESCE(p.pg_name, '') program_name, l.order_num " +
                    " from station_program_rel a, station_program_set_rel l, program_set_info p " +
                    " where a.station_id = l.station_id and a.program_id = l.program_id and l.pg_type = 'TV' and l.sid = p.sid " +
                    " order by a.station_id, a.program_id, l.order_num";
                List<Dictionary<string, string>> lsData = ExecReader(dbConnection, sql);
                //以台站分组
                foreach (Dictionary<string, string> data in lsData)
                {
                    //台站
                    string station_id = data.ContainsKey("station_id") ? data["station_id"] : "";
                    //频率
                    string frequency = data["frequency"];
                    if (!dtvProgram.ContainsKey(station_id))
                    {
                        dtvProgram.TryAdd(station_id, new());
                    }

                    if (!dtvProgram[station_id].ContainsKey(frequency))
                    {
                        dtvProgram[station_id].TryAdd(frequency, new());
                    }

                    dtvProgram[station_id][frequency].Add(data.Where(d => d.Key.ToUpper().Equals("SID") || d.Key.ToUpper().Equals("PROGRAM_ID") || d.Key.ToUpper().Equals("PROGRAM_NAME") || d.Key.ToUpper().Equals("FREQUENCY")).ToDictionary(d => d.Key, d => d.Value));
                }
            }
            catch (Exception e)
            {
                dtvProgram = null;
                CommonUtil.mLogger.LogError("从station_program_rel，station_program_set_rel获取数据失败！" + e.Message);
            }
            return dtvProgram;
        }

        /// <summary>
        /// 获取节目信息
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, string>> GetProgramInfo(DbConnection dbConnection)
        {
            Dictionary<string, Dictionary<string, string>> programInfo = new();
            try
            {
                //所有参数
                string sql = "select a.program_id, a.program_name, a.summary, a.program_level, a.remarks from program_info a";
                List<Dictionary<string, string>> lsData = ExecReader(dbConnection, sql);
                //以台站分组
                foreach (Dictionary<string, string> data in lsData)
                {
                    //节目id
                    string program_id = data["program_id"];
                    programInfo.TryAdd(program_id, data);
                }
            }
            catch (Exception e)
            {
                programInfo = null;
                CommonUtil.mLogger.LogError("从program_info获取数据失败！" + e.Message);
            }
            return programInfo;
        }

        /// <summary>
        /// 获取台站-频率时间表数据
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, Dictionary<string, List<Dictionary<string, string>>>>> GetTimesTable(DbConnection dbConnection, out Dictionary<string, Dictionary<string, Dictionary<string, List<Dictionary<string, string>>>>> programTimesTables)
        {
            Dictionary<string, Dictionary<string, Dictionary<string, List<Dictionary<string, string>>>>> timesTables = new();
            programTimesTables = new();
            try
            {
                //所有参数
                string sql = "select a.id, a.station_id, a.frequency, a.program_id, a.show_scene, a.show_date, a.show_start_time, a.show_end_time, a.is_valid, a.remarks from station_program_time a" +
                    " where is_valid = '1'" +
                    " order by a.station_id,frequency, show_date, id";
                List<Dictionary<string, string>> lsData = ExecReader(dbConnection, sql);
                //以台站分组
                foreach (Dictionary<string, string> data in lsData)
                {
                    //台站
                    string station_id = data["station_id"];
                    //频率
                    string frequency = data["frequency"];
                    string programId = data["program_id"];
                    //星期几
                    string weekN = data["show_date"];
                    if (!timesTables.ContainsKey(station_id))
                    {
                        timesTables.TryAdd(station_id, new Dictionary<string, Dictionary<string, List<Dictionary<string, string>>>>());
                        programTimesTables.TryAdd(station_id, new Dictionary<string, Dictionary<string, List<Dictionary<string, string>>>>());
                    }

                    if (!timesTables[station_id].ContainsKey(frequency))
                    {
                        timesTables[station_id].TryAdd(frequency, new Dictionary<string, List<Dictionary<string, string>>>() { });
                    }
                    if (!programTimesTables[station_id].ContainsKey(programId))
                    {
                        programTimesTables[station_id].TryAdd(programId, new Dictionary<string, List<Dictionary<string, string>>>() { });
                    }

                    if (!timesTables[station_id][frequency].ContainsKey(weekN))
                    {
                        timesTables[station_id][frequency].TryAdd(weekN, new List<Dictionary<string, string>>());
                    }
                    if (!programTimesTables[station_id][programId].ContainsKey(weekN))
                    {
                        programTimesTables[station_id][programId].TryAdd(weekN, new List<Dictionary<string, string>>());
                    }
                    timesTables[station_id][frequency][weekN].Add(data);
                    programTimesTables[station_id][programId][weekN].Add(data.Where(d => d.Key.Equals("show_start_time") || d.Key.Equals("show_end_time")).ToDictionary(d => d.Key.Substring(d.Key.IndexOf("_") + 1), d => d.Value));
                }
            }
            catch (Exception e)
            {
                timesTables = null;
                CommonUtil.mLogger.LogError("从station_program_time获取数据失败！" + e.Message);
            }
            return timesTables;
        }

        /// <summary>
        /// 获取关联告警信息
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static List<Dictionary<string, Object>> GetRelationAlarmInfo(DbConnection dbConnection)
        {
            List<Dictionary<string, Object>> relationAlarmInfos = new();
            try
            {
                //所有参数
                //string sql = "select * from derived_alarm_info a where is_used = '1' order by derived_alarm_id";
                //string sql = "select * from derived_alarm_info a where 1=1 order by relation_method";
                //string sql = "select a.*, b.device_type_id, b.alarm_level, b.alarm_title, b.alarm_type, c.dictionary_name sub_system from derived_alarm_info a, alarm_basic_info b, dictionary_info c " +
                //    "where a.alarm_id=b.alarm_id and b.sub_system_id = c.dictionary_value and c.dictionary_type = '1' and c.dictionary_english_name = 'sub_system' order by relation_method";
                string sql = "select a.*, b.device_type_id, b.alarm_level, b.alarm_title, b.alarm_type, c.dictionary_name sub_system, d.alarm_title_id, e.device_type_name" +
                    " from derived_alarm_info a, alarm_basic_info b, dictionary_info c, alarm_title_table d, device_type_info e " +
                    "where a.alarm_id=b.alarm_id and b.sub_system_id = c.dictionary_value and b.alarm_title_id = d.alarm_title_id and c.dictionary_type = '1' and " +
                    "c.dictionary_english_name = 'sub_system' and b.device_type_id = e.device_type_id order by a.derived_alarm_id";
                List<Dictionary<string, string>> lsData = ExecReader(dbConnection, sql);

                //以台站分组
                foreach (Dictionary<string, string> data in lsData)
                {
                    Dictionary<string, Object> relationAlarmInfo = JsonConvert.DeserializeObject<Dictionary<string, Object>>(JsonConvert.SerializeObject(data));
                    if (data.ContainsKey("source_alarm_condition_id_array") && !"".Equals(data["source_alarm_condition_id_array"]))
                    {
                        //关联ID 表source_alarm_condition_info的id，逗号分割(,)
                        string ids = data["source_alarm_condition_id_array"];

                        string tmpsql = "select * from source_alarm_condition_info a where source_alarm_condition_id in('" + ids + "') order by source_alarm_condition_id";
                        List<Dictionary<string, string>> rlInfo = ExecReader(dbConnection, tmpsql);
                        relationAlarmInfo.TryAdd("rConditionArray", rlInfo);    //添加关联数据
                    }
                    else
                    {
                        relationAlarmInfo.TryAdd("rConditionArray", new List<Dictionary<string, string>>());    //添加关联数据
                    }

                    relationAlarmInfos.Add(relationAlarmInfo);
                }
            }
            catch (Exception e)
            {
                relationAlarmInfos = null;
                CommonUtil.mLogger.LogError("获取关联告警信息失败！" + e.Message);
            }
            return relationAlarmInfos;
        }

        /// <summary>
        /// 过滤器信息
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, List<Dictionary<string, string>>>> GetFilterInfo(DbConnection dbConnection, string unicode = "")
        {
            Dictionary<string, Dictionary<string, List<Dictionary<string, string>>>> filterInfo = new();
            try
            {
                //明细参数
                string sql = " select a.parent_id, a.sub_no, a.sub_val from filter_sub a  " +
                    " where a.parent_id <> -1 and EXISTS(select 1 from filter_main b where a.main_id = b.id and b.used = true and b.newest = true " + ("".Equals(unicode) ? "" : " and b.unique_code = '" + unicode + "'") + ") " +
                    " order by main_id, parent_id, id";
                List<Dictionary<string, string>> lsDetail = ExecReader(dbConnection, sql);
                Dictionary<string, List<Dictionary<string, string>>> subDetail = new();
                foreach (Dictionary<string, string> data in lsDetail)
                {
                    string parent_id = data["parent_id"];
                    if (!subDetail.ContainsKey(parent_id))
                    {
                        subDetail.TryAdd(parent_id, new List<Dictionary<string, string>>());
                    }
                    subDetail[parent_id].Add(data);
                }

                //所有参数
                sql = " select b.unique_code, a.sub_no, a.sub_val, a.id " +
                    " from filter_sub a, filter_main b " +
                    " where a.parent_id=-1 and a.main_id = b.id and b.used = true and b.newest = true " + ("".Equals(unicode) ? "" : " and b.unique_code = '" + unicode + "'") +
                    " order by a.main_id, a.parent_id, a.id";
                List<Dictionary<string, string>> lsData = ExecReader(dbConnection, sql);

                //过滤器唯一code分组
                foreach (Dictionary<string, string> data in lsData)
                {
                    string id = data["id"];
                    string sub_no = data["sub_no"];
                    string unique_code = data["unique_code"];
                    if (!filterInfo.ContainsKey(unique_code))
                    {
                        filterInfo.TryAdd(unique_code, new Dictionary<string, List<Dictionary<string, string>>>());    //添加关联数据  
                    }

                    if (!filterInfo[unique_code].ContainsKey(sub_no))
                    {
                        filterInfo[unique_code].TryAdd(sub_no, new List<Dictionary<string, string>>());
                    }

                    if (subDetail.ContainsKey(id))
                    {
                        filterInfo[unique_code][sub_no] = subDetail[id];
                    }
                }
            }
            catch (Exception e)
            {
                filterInfo = null;
                CommonUtil.mLogger.LogError("获取过滤器数据失败！" + e.Message);
            }
            return filterInfo;
        }

        /// <summary>
        /// 获取字典数据
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, string>> GetDictInfo(DbConnection dbConnection)
        {
            Dictionary<string, Dictionary<string, string>> dictInfo = new();
            try
            {
                //子系统\station_level\alarm_level
                string sql = "select dictionary_english_name, dictionary_id, dictionary_name, dictionary_value from dictionary_info a " +
                    //"where a.dictionary_type = 1 and a.dictionary_english_name in ('sub_system', 'station_level', 'alarm_level') " +
                    "where a.dictionary_type = 1 " +
                    "order by dictionary_english_name, dictionary_value";
                List<Dictionary<string, string>> dictInfos = ExecReader(dbConnection, sql);
                //字典数据
                //Dictionary<string, List<Dictionary<string, string>>> dictInfoAll = new Dictionary<string, List<Dictionary<string, string>>>();
                foreach (var info in dictInfos)
                {
                    string name = info["dictionary_english_name"];
                    string dictkey = info["dictionary_value"];
                    string dictVal = info["dictionary_name"];
                    if (!dictInfo.ContainsKey(name))
                    {
                        dictInfo.TryAdd(name, new Dictionary<string, string>());
                    }
                    if (!dictInfo[name].ContainsKey(dictkey))
                    {
                        dictInfo[name].TryAdd(dictkey, dictVal);
                    }
                }
            }
            catch (Exception e)
            {
                dictInfo = null;
                CommonUtil.mLogger.LogError("获取字典数据异常，" + e.Message);
            }
            return dictInfo;
        }

        /// <summary>
        /// 子系统和设备类型配置数据
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, string>> GetSubSystemDeviceInfo(DbConnection dbConnection)
        {
            Dictionary<string, Dictionary<string, string>> subSystemDeviceInfo = new();
            try
            {
                string sql = "select sub_system_id, device_type_id, device_codes from device_type_info order by sub_system_id, device_type_id";
                List<Dictionary<string, string>> dictInfos = ExecReader(dbConnection, sql);
                //字典数据
                foreach (var detail in dictInfos)
                {
                    string subSystemId = detail["sub_system_id"];
                    string devCodes = detail["device_codes"];
                    if (!subSystemDeviceInfo.ContainsKey(subSystemId))
                    {
                        subSystemDeviceInfo.TryAdd(subSystemId, new());
                    }
                    foreach (string device in devCodes.Split(','))
                    {
                        if (!"".Equals(device))
                        {
                            if (!subSystemDeviceInfo[subSystemId].ContainsKey(device))
                            {
                                subSystemDeviceInfo[subSystemId].TryAdd(device, "");
                            }
                        }
                    }
                }
            }
            catch (Exception e)
            {
                subSystemDeviceInfo = null;
                CommonUtil.mLogger.LogError("子系统关联设备类型数据异常，" + e.Message);
            }
            return subSystemDeviceInfo;
        }

        /// <summary>
        /// 获取组织信息
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static List<Dictionary<string, string>> GetDeptInfo(DbConnection dbConnection)
        {
            List<Dictionary<string, string>> deptInfo;
            try
            {
                string sql = "select dept_id, COALESCE(parent_dept_id, -1) parent_dept_id, dept_name, COALESCE(dept_level, -1) dept_level, dept_order from dept order by dept_level, parent_dept_id, dept_order";
                deptInfo = ExecReader(dbConnection, sql);
            }
            catch (Exception e)
            {
                deptInfo = null;
                CommonUtil.mLogger.LogError("获取组织信息异常，" + e.Message);
            }
            return deptInfo;
        }

        /// <summary>
        /// 获取过滤器初始化配置
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Object> GetFilterInitInfo(DbConnection dbConnection)
        {
            Dictionary<string, Object> filterInitInfo = new();
            try
            {
                //分中心 维护部
                string sql = "select b.dept_id sub_id, b.dept_name sub_name, a.dept_id, a.dept_name from dept a, dept b  " +
                    "where a.parent_dept_id in " +
                    "( " +
                    "	select dept_id from dept a where a.parent_dept_id in " +
                    "	(select dept_id from dept a where a.parent_dept_id in(select dept_id from dept a where a.dept_level = 1)) " +
                    ") " +
                    "and a.parent_dept_id = b.dept_id " +
                    "order by b.dept_id, b.dept_order, a.dept_id, a.dept_order";
                List<Dictionary<string, string>> subDeptInfos = ExecReader(dbConnection, sql);
                //分中心数据
                Dictionary<string, string> tmpDict = new();
                List<Dictionary<string, string>> subInfo = new();
                //维护部数据 按分中心分组
                //Dictionary<string, List<Dictionary<string, string>>> deptInfo = new Dictionary<string, List<Dictionary<string, string>>>();
                List<Dictionary<string, string>> deptInfo = new();
                foreach (var info in subDeptInfos)
                {
                    string sub_id = info["sub_id"];
                    string sub_name = info["sub_name"];
                    if (!tmpDict.ContainsKey(sub_id))
                    {
                        tmpDict.TryAdd(sub_id, "");
                        subInfo.Add(new Dictionary<string, string>() { { "sub_id", sub_id }, { "sub_name", sub_name } });
                    }
                    //if (!deptInfo.ContainsKey(sub_id))
                    //{
                    //    deptInfo.TryAdd(sub_id, new List<Dictionary<string, string>>());
                    //}
                    info.Remove("sub_name");
                    //deptInfo[sub_id].Add(info);
                    deptInfo.Add(info);
                }
                //部门信息
                filterInitInfo.TryAdd("subInfo", subInfo);
                //部门找到维护部
                filterInitInfo.TryAdd("deptInfo", deptInfo);
                //根据维护部找到部门
                //filterInitInfo.TryAdd("deptSubInfo", subDeptInfos.ToDictionary(x => x["dept_id"], y => y));

                //台站信息
                //sql = "select a.station_id, a.subcenter_id, a.dept_id, a.station_name, a.station_level from station a where a.is_monitor = 1 order by a.station_level, a.subcenter_id, a.dept_id, a.station_id";
                sql = "select a.station_id, a.subcenter_id, a.dept_id, a.station_name, a.station_level from station a order by a.station_level, a.subcenter_id, a.dept_id, a.station_id";
                List<Dictionary<string, string>> station = ExecReader(dbConnection, sql);
                //Dictionary<string, List<Dictionary<string, string>>> stationInfo = new Dictionary<string, List<Dictionary<string, string>>>();
                //foreach (var info in station)
                //{
                //    string subcenter_id = info["subcenter_id"];
                //    string dept_id = info.ContainsKey("dept_id") ? info["dept_id"] : "";
                //    string key = subcenter_id + "-" + dept_id;
                //    if (!stationInfo.ContainsKey(key))
                //    {
                //        stationInfo.TryAdd(key, new List<Dictionary<string, string>>());
                //    }
                //    info.Remove("subcenter_id");
                //    info.Remove("dept_id");
                //    stationInfo[key].Add(info);
                //}
                //filterInitInfo.TryAdd("stationInfo", stationInfo);
                filterInitInfo.TryAdd("stationInfo", station);

                //设备类型信息
                sql = "select device_type_id, device_type_name, sub_system_id from device_type_info a order by device_type_id";
                List<Dictionary<string, string>> deviceInfo = ExecReader(dbConnection, sql);
                //filterInitInfo.TryAdd("deviceInfo", deviceInfo.ToDictionary(x => x["device_type_id"], y => y["device_type_name"]));
                filterInitInfo.TryAdd("deviceInfo", deviceInfo);

                //告警标题
                //sql = "select alarm_id, alarm_title, alarm_level, sub_system_id, device_type_id from alarm_basic_info a where alarm_type>0 order by alarm_id";
                //sql = "select a.alarm_id, b.alarm_title, a.alarm_level, a.sub_system_id, a.device_type_id from alarm_basic_info a, alarm_title_table b where a.alarm_title_id = b.alarm_title_id and alarm_type>0 order by alarm_id";
                sql = "select distinct b.alarm_title_id,b.alarm_title from alarm_basic_info a, alarm_title_table b where a.alarm_title_id = b.alarm_title_id and alarm_type>0 order by b.alarm_title_id";
                List<Dictionary<string, string>> alarmInfo = ExecReader(dbConnection, sql);
                //filterInitInfo.TryAdd("alarmInfo", alarmInfo.ToDictionary(x => x["alarm_id"], y => y["alarm_title"]));
                filterInitInfo.TryAdd("alarmInfo", alarmInfo);

                //子系统\station_level\alarm_level
                sql = "select dictionary_english_name, dictionary_id, dictionary_name, dictionary_value from dictionary_info a " +
                    "where a.dictionary_type = 1 and a.dictionary_english_name in ('sub_system', 'station_level', 'alarm_level','cover_mode') " +
                    //"where a.dictionary_type = 1 " +
                    "order by dictionary_english_name, dictionary_value";
                List<Dictionary<string, string>> dictInfos = ExecReader(dbConnection, sql);
                //字典数据
                //Dictionary<string, List<Dictionary<string, string>>> dictInfoAll = new Dictionary<string, List<Dictionary<string, string>>>();
                foreach (var info in dictInfos)
                {
                    string name = info["dictionary_english_name"];
                    if (!filterInitInfo.ContainsKey(name))
                    {
                        filterInitInfo.TryAdd(name, new List<Dictionary<string, string>>());
                    }
                    ((List<Dictionary<string, string>>)filterInitInfo[name]).Add(info);
                }
            }
            catch (Exception e)
            {
                filterInitInfo = null;
                CommonUtil.mLogger.LogError("获取过滤器初始化数据异常，" + e.Message);
            }
            return filterInitInfo;
        }

        /// <summary>
        /// 获取过滤器明细，用于过滤告警
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, Dictionary<string, string>>> GetFilterDetail(DbConnection dbConnection, List<string> filterUnicodes = null)
        {
            Dictionary<string, Dictionary<string, Dictionary<string, string>>> filterDetail = new();
            try
            {
                string sql = "select a.id, a.unique_code, c.sub_no p_sub_no, c.sub_val p_sub_val, b.sub_no, b.sub_val " +
                    "from filter_main a, filter_sub b, filter_sub c " +
                    "where a.id = b.main_id and b.parent_id <> -1  " +
                    "and b.parent_id = c.id " +
                    "and a.used = true and a.newest = true " + (filterUnicodes == null ? "" : (" and a.unique_code in('" + string.Join("','", filterUnicodes.ToArray()) + "') ")) +
                    "order by a.id, b.parent_id, b.id";
                List<Dictionary<string, string>> lsDetail = ExecReader(dbConnection, sql);
                foreach (var detail in lsDetail)
                {
                    string unicode = detail["unique_code"];
                    string subtype = detail["p_sub_no"];
                    string subno = detail["sub_no"];
                    if (!filterDetail.ContainsKey(unicode))
                    {
                        filterDetail.TryAdd(unicode, new Dictionary<string, Dictionary<string, string>>());
                    }
                    if (!filterDetail[unicode].ContainsKey(subtype))
                    {
                        filterDetail[unicode].TryAdd(subtype, new Dictionary<string, string>());
                    }
                    filterDetail[unicode][subtype].TryAdd(subno, "");
                }
            }
            catch (Exception e)
            {
                CommonUtil.mLogger.LogError("获取过滤器明细异常，" + e.Message);
            }
            return filterDetail;
        }

        /// <summary>
        /// 获取告警布局信息
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, List<Dictionary<string, string>>> GetAlarmLayoutInfo(DbConnection dbConnection, string opCode = "", string deptId = "")
        {
            Dictionary<string, List<Dictionary<string, string>>> alarmLayoutInfo = new Dictionary<string, List<Dictionary<string, string>>>();
            try
            {
                string sql = "select id, op_code, get_time, layout_name, layout_info, filters_code, case privated when '1' then 'true' when '0' then 'false' else privated||'' end privated, dept_id, filters_name, COALESCE(agent_type, 1) agent_type " +
                    "from alarm_layout_info a where 1=1 " + ("".Equals(opCode) ? "" : " and (op_code = '" + opCode + "' or privated = false)") + " order by get_time desc";
                List<Dictionary<string, string>> lsDetail = ExecReader(dbConnection, sql);
                //共有的布局
                List<Dictionary<string, string>> lsPublic = new();
                foreach (var detail in lsDetail)
                {
                    string op = detail["op_code"];
                    string privated = detail["privated"].ToUpper();

                    if ("FALSE".Equals(privated))
                    {
                        lsPublic.Add(detail);
                    }

                    //各个操作员的
                    if (((!"".Equals(opCode) && opCode.Equals(op)) || "".Equals(opCode)))
                    {
                        if (!alarmLayoutInfo.ContainsKey(op))
                        {
                            alarmLayoutInfo.TryAdd(op, new List<Dictionary<string, string>>());
                        }
                        alarmLayoutInfo[op].Add(detail);
                    }
                }

                //共有的，加到每一个用户下
                foreach (var op in alarmLayoutInfo.Keys)
                {
                    string opDept = alarmLayoutInfo[op][0]["dept_id"];
                    foreach (var pub in lsPublic)
                    {
                        //同一个分中心下的，实现共享
                        string pubOp = pub["op_code"];
                        //同一分中心，不同用户的
                        if (!op.Equals(pubOp) && opDept.Equals(pub["dept_id"]))
                        {
                            alarmLayoutInfo[op].Add(pub);
                        }
                    }
                }

                //有传递用户opCode，但是用户又没有新增任何布局，无法读取分中心dept_id
                if (!"".Equals(deptId) && !"".Equals(opCode) && !alarmLayoutInfo.ContainsKey(opCode))
                {
                    foreach (var pub in lsPublic)
                    {
                        if (pub.ContainsKey("dept_id") && deptId.Equals(pub["dept_id"]))
                        {
                            if (!alarmLayoutInfo.ContainsKey(opCode))
                            {
                                alarmLayoutInfo.TryAdd(opCode, new List<Dictionary<string, string>>());
                            }
                            alarmLayoutInfo[opCode].Add(pub);
                        }
                    }
                }
            }
            catch (Exception e)
            {
                alarmLayoutInfo = null;
                CommonUtil.mLogger.LogError("获取告警布局信息异常，" + e.Message);
            }
            return alarmLayoutInfo;
        }

        /// <summary>
        /// 获取告警布局信息
        /// 只返回已部门dept_id分组的共享布局
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <param name="dept_id"></param>
        /// <returns></returns>
        public static Dictionary<string, List<Dictionary<string, string>>> GetDeptAlarmLayoutInfo(DbConnection dbConnection, string dept_id = "")
        {
            Dictionary<string, List<Dictionary<string, string>>> alarmLayoutInfo = new Dictionary<string, List<Dictionary<string, string>>>();
            try
            {
                string sql = "select id, op_code, get_time, layout_name, layout_info, filters_code, case privated when '1' then 'true' when '0' then 'false' else privated||'' end privated, dept_id, filters_name, COALESCE(agent_type, 1) agent_type " +
                    " from alarm_layout_info a where privated = false " + ("".Equals(dept_id) ? "" : " and dept_id='" + dept_id + "'") + " order by get_time desc";
                List<Dictionary<string, string>> lsDetail = ExecReader(dbConnection, sql);
                //共有的布局
                List<Dictionary<string, string>> lsPris = new();
                foreach (var detail in lsDetail)
                {
                    string deptId = detail["dept_id"];
                    string privated = detail["privated"].ToUpper();
                    if (!alarmLayoutInfo.ContainsKey(deptId))
                    {
                        alarmLayoutInfo.TryAdd(deptId, new List<Dictionary<string, string>>());
                    }
                    alarmLayoutInfo[deptId].Add(detail);
                }
            }
            catch (Exception e)
            {
                alarmLayoutInfo = null;
                CommonUtil.mLogger.LogError("获取部门告警布局信息异常，" + e.Message);
            }
            return alarmLayoutInfo;
        }

        /// <summary>
        /// 获取告警列表配置信息
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, GridColumnsMode> GetAlarmColSetInfo(DbConnection dbConnection)
        {
            Dictionary<string, GridColumnsMode> colSetInfo = new();
            try
            {
                string sql = "select filter_unique_code,op_code, columns_set,agent_type from alarm_column_set order by id";
                List<Dictionary<string, string>> lsColSet = ExecReader(dbConnection, sql);
                foreach (var colSet in lsColSet)
                {
                    string filterUnicode = colSet["filter_unique_code"];
                    string userId = colSet["op_code"];
                    string columns_set = colSet["columns_set"];
                    int agentType = int.Parse(colSet["agent_type"]);
                    string columnSetKey = "ColumnSet_" + filterUnicode + "_" + userId + "_" + agentType;
                    colSetInfo.TryAdd(columnSetKey, new GridColumnsMode() { Filter_unique_code = filterUnicode, userID = userId, columnModes = JsonConvert.DeserializeObject<List<ColumnMode>>(columns_set), agentType= agentType });
                }
            }
            catch (Exception e)
            {
                colSetInfo = null;
                CommonUtil.mLogger.LogError("获取告警列表配置信息异常，" + e.Message);
            }
            return colSetInfo;
        }

        /// <summary>
        /// 获取命令参数明细
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, string>> GetIssuePara(DbConnection dbConnection)
        {
            Dictionary<string, Dictionary<string, string>> issuePara = new();
            try
            {
                string sql = "select id,get_time,para_code,para_type,para_length,para_name,case is_need when '1' then 'true' when '0' then 'false' else is_need||'' end is_need," +
                    "default_val,example_val,source_code,case from_svr when '1' then 'true' when '0' then 'false' else from_svr||'' end from_svr,calc_type,detail,reserve2,memo " +
                    "from cmd_issue_para a order by id";
                List<Dictionary<string, string>> lsDetail = ExecReader(dbConnection, sql);
                foreach (var detail in lsDetail)
                {
                    string id = detail["id"];
                    if (!issuePara.ContainsKey(id))
                    {
                        issuePara.TryAdd(id, detail);
                    }
                }
            }
            catch (Exception e)
            {
                issuePara = null;
                CommonUtil.mLogger.LogError("获取命令参数明细异常，" + e.Message);
            }
            return issuePara;
        }

        /// <summary>
        /// 获取命令模板信息
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, string>> GetIssueTemplate(DbConnection dbConnection)
        {
            Dictionary<string, Dictionary<string, string>> issueTemplates = new();
            try
            {
                string sql = "select id,cmd_text,COALESCE(dft_paras, '') dft_paras,memo,COALESCE(template_key, '') template_key from cmd_template order by id";
                List<Dictionary<string, string>> lsDetail = ExecReader(dbConnection, sql);
                foreach (var detail in lsDetail)
                {
                    string id = detail["id"];
                    if (!issueTemplates.ContainsKey(id))
                    {
                        issueTemplates.TryAdd(id, detail);
                    }
                }
            }
            catch (Exception e)
            {
                issueTemplates = null;
                CommonUtil.mLogger.LogError("获取命令模板信息异常，" + e.Message);
            }
            return issueTemplates;
        }

        /// <summary>
        /// 获取设备下发信息
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, Dictionary<string, string>>> GetDevIssueInfo(DbConnection dbConnection)
        {
            Dictionary<string, Dictionary<string, Dictionary<string, string>>> devIssueInfo = new Dictionary<string, Dictionary<string, Dictionary<string, string>>>();
            try
            {
                string sql = "select id,device_type,cmd_type,template_ids,case used when '1' then 'true' when '0' then 'false' else used||'' end used,get_time,memo " +
                    "from device_cmd_issue a where a.used = true order by id";
                List<Dictionary<string, string>> lsDetail = ExecReader(dbConnection, sql);
                foreach (var detail in lsDetail)
                {
                    string device_type = detail["device_type"];
                    string cmd_type = detail["cmd_type"];
                    if (!devIssueInfo.ContainsKey(device_type))
                    {
                        devIssueInfo.TryAdd(device_type, new Dictionary<string, Dictionary<string, string>>());
                    }
                    devIssueInfo[device_type].TryAdd(cmd_type, detail);
                }
            }
            catch (Exception e)
            {
                devIssueInfo = null;
                CommonUtil.mLogger.LogError("获取命令模板信息异常，" + e.Message);
            }
            return devIssueInfo;
        }

        /// <summary>
        /// 获取特殊告警模板信息
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, Dictionary<string, Dictionary<string, Dictionary<string, string>>>>> GetModelManagement(DbConnection dbConnection)
        {
            Dictionary<string, Dictionary<string, Dictionary<string, Dictionary<string, Dictionary<string, string>>>>> modelManagement = new();
            try
            {
                string sql = "select * from model_management a order by station_id, position_id, model_id, para_name";
                List<Dictionary<string, string>> lsDetail = ExecReader(dbConnection, sql);
                foreach (var detail in lsDetail)
                {
                    string station_id = detail["station_id"];
                    string model_id = detail["model_id"];
                    string position_id = detail["position_id"];
                    string para_name = detail["para_name"];
                    if (!modelManagement.ContainsKey(station_id))
                    {
                        modelManagement.TryAdd(station_id, new());
                    }
                    if (!modelManagement[station_id].ContainsKey(model_id))
                    {
                        modelManagement[station_id].TryAdd(model_id, new());
                    }
                    if (!modelManagement[station_id][model_id].ContainsKey(position_id))
                    {
                        modelManagement[station_id][model_id].TryAdd(position_id, new());
                    }
                    if (!modelManagement[station_id][model_id][position_id].ContainsKey(para_name))
                    {
                        modelManagement[station_id][model_id][position_id].TryAdd(para_name, detail);
                    }
                }
            }
            catch (Exception e)
            {
                modelManagement = null;
                CommonUtil.mLogger.LogError("获取特殊告警模板信息异常，" + e.Message);
            }
            return modelManagement;
        }

        /// <summary>
        /// 获取station_node所有的工位号
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, string> GetNodePositonids(DbConnection dbConnection)
        {
            Dictionary<string, string> positionIds = new();
            try
            {
                string sql = "select distinct position_id from (select distinct relation_position_id position_id from station_node union all select distinct position_id from station_node) a where position_id !='' order by position_id";
                List<Dictionary<string, string>> lsDetail = ExecReader(dbConnection, sql);
                positionIds = lsDetail.ToDictionary(d => d["position_id"], d => "");
            }
            catch (Exception e)
            {
                CommonUtil.mLogger.LogError("获取station_node所有的工位号异常，" + e.Message);
            }
            return positionIds;
        }

        /// <summary>
        /// 获取台站设备类型可以拥有的最大工位编号
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, int>> GetDeviceMaxPosition(DbConnection dbConnection)
        {
            Dictionary<string, Dictionary<string, int>> devMaxPositionInfos = new Dictionary<string, Dictionary<string, int>>();
            try
            {
                string sql = "select a.station_level, a.dev_type, max(work_no) work_no from position_id_match a where a.dev_type is not null and a.dev_type<>'' group by a.station_level, a.dev_type order by a.station_level, a.dev_type";
                List<Dictionary<string, string>> lsDetail = ExecReader(dbConnection, sql);
                foreach (var detail in lsDetail)
                {
                    string station_level = detail["station_level"];
                    string[] devtypes = detail["dev_type"].Split(',');
                    string work_no = detail["work_no"];
                    if (!devMaxPositionInfos.ContainsKey(station_level))
                    {
                        devMaxPositionInfos.TryAdd(station_level, new());
                    }
                    foreach (string dev_type in devtypes)
                    { 
                        if (!devMaxPositionInfos[station_level].ContainsKey(dev_type))
                        {
                            devMaxPositionInfos[station_level].TryAdd(dev_type, int.Parse(work_no));
                        }
                    }
                }
            }
            catch (Exception e)
            {
                devMaxPositionInfos = null;
                CommonUtil.mLogger.LogError("获取设备类型最大工位编号异常，" + e.Message);
            }
            return devMaxPositionInfos;
        }

        /// <summary>
        /// 获取海康威视信息
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, List<Dictionary<string, string>>> GetStationHIKRec(DbConnection dbConnection, string stationId = "")
        {
            Dictionary<string, List<Dictionary<string, string>>> stationHIKRec = new();
            try
            {
                string sql = "select station_id, video_ip, control_port, video_name, COALESCE(video_pawd, '') video_pawd, is_online from station_video_rec where 1=1 " + 
                    ((stationId != null && !"".Equals(stationId)) ? " and station_id  ='" + stationId + "'" : "") + " order by station_id, video_ip";
                List<Dictionary<string, string>> lsDetail = ExecReader(dbConnection, sql);
                foreach (var detail in lsDetail)
                {
                    string staId = detail["station_id"];
                    if (!stationHIKRec.ContainsKey(staId))
                    {
                        stationHIKRec.TryAdd(staId, new());
                    }
                    stationHIKRec[staId].Add(detail);
                }
            }
            catch (Exception e)
            {
                stationHIKRec = null;
                CommonUtil.mLogger.LogError("获取海康威视通道信息数据异常，" + e.Message);
            }
            return stationHIKRec;
        }

        /// <summary>
        /// 获取海康威视1-3级信号源数据
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, List<Dictionary<string, string>>> GetHIKSourceData(DbConnection dbConnection)
        {
            Dictionary<string, List<Dictionary<string, string>>> HIKSourceDatas = new();
            try
            {
                string sql = "SELECT" +
                    "	r.station_id," +
                    "	'0xFD01' dev_type," +
                    "	r.frequency," +
                    "	r.program_name," +
                    "	r.in_channel," +
                    "	r.out_channel," +
                    "	vr.video_ip," +
                    "	vr.control_port," +
                    "	vr.video_name," +
                    "	COALESCE ( vr.video_pawd, '' ) video_pawd," +
                    "	r.show_type," +
                    "	r.order_num, " +
                    "	r.program_type " +
                    " FROM" +
                    "	(" +
                    "	SELECT" +
                    "		R.*," +
                    "		P.program_name " +
                    "	FROM" +
                    "		station_program_rel R," +
                    "		program_info P " +
                    "	WHERE" +
                    "		R.program_id = P.program_id " +
                    "		AND r.station_id IN ( SELECT s.station_id FROM station s WHERE s.station_level IN ( '1', '2', '3' ) ) " +
                    "		AND R.show_type IN ( '1', '2', '3' ) " +
                    "	) r" +
                    "	LEFT JOIN station_video_rec vr ON vr.ID = r.video_recorder_id " +
                    "	AND vr.station_id = r.station_id " +
                    " ORDER BY" +
                    "	r.station_id," +
                    "	r.show_type," +
                    "	r.order_num";
                List<Dictionary<string, string>> lsDetail = ExecReader(dbConnection, sql);
                int idx = 0;
                foreach (var detail in lsDetail)
                {
                    string stationId = detail["station_id"];
                    string devType = detail["dev_type"];
                    string positionId = stationId + "_" + devType + "_1";
                    if (!HIKSourceDatas.ContainsKey(positionId))
                    {
                        idx = 0;
                        HIKSourceDatas.TryAdd(positionId, new());
                    }
                    HIKSourceDatas[positionId].Add(detail.ToDictionary(d => d.Key.ToUpper() + "_" + idx, d => d.Value ?? ""));
                    idx++;
                }
            }
            catch (Exception e)
            {
                HIKSourceDatas = null;
                CommonUtil.mLogger.LogError("获取海康威视1-3级信号源数据异常，" + e.Message);
            }
            return HIKSourceDatas;
        }

        /// <summary>
        /// 获取海康威视1-3级收测数据
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, List<Dictionary<string, string>>> GetHIKReceiveData(DbConnection dbConnection)
        {
            Dictionary<string, List<Dictionary<string, string>>> HIKReceiveDatas = new();
            try
            {
                string sql = "SELECT" +
                    "	r.station_id," +
                    "	'0xFE01' dev_type," +
                    "	r.frequency," +
                    "	r.video_ip," +
                    "	r.video_port," +
                    "	r.video_name," +
                    "	COALESCE ( r.video_pasd, '' ) video_pasd," +
                    "	r.video_in_channel " +
                    " FROM" +
                    "	receive_video_info r " +
                    " WHERE" +
                    "	r.station_id IN ( SELECT s.station_id FROM station s WHERE s.station_level IN ( '1', '2', '3' ) ) " +
                    " ORDER BY" +
                    "	r.station_id," +
                    "	r.video_ip," +
                    "	r.video_in_channel";
                List<Dictionary<string, string>> lsDetail = ExecReader(dbConnection, sql);
                int idx = 0;
                foreach (var detail in lsDetail)
                {
                    string stationId = detail["station_id"];
                    string devType = detail["dev_type"];
                    string positionId = stationId + "_" + devType + "_1";
                    if (!HIKReceiveDatas.ContainsKey(positionId))
                    {
                        idx = 0;
                        HIKReceiveDatas.TryAdd(positionId, new());
                    }
                    HIKReceiveDatas[positionId].Add(detail.ToDictionary(d => d.Key.ToUpper() + "_" + idx, d => d.Value ?? ""));
                    idx++;
                }
            }
            catch (Exception e)
            {
                HIKReceiveDatas = null;
                CommonUtil.mLogger.LogError("获取海康威视1-3级收测数据异常，" + e.Message);
            }
            return HIKReceiveDatas;
        }

        /// <summary>
        /// 获取海康威视4级收测数据
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, List<Dictionary<string, string>>> GetHIKFourReceiveData(DbConnection dbConnection)
        {
            Dictionary<string, List<Dictionary<string, string>>> HIKFourReceiveDatas = new();
            try
            {
                string sql = "SELECT" +
                    "	r.station_id," +
                    "	'0xFE02' dev_type," +
                    "	r.frequency," +
                    "	r.program_name," +
                    "	r.in_channel," +
                    "	r.out_channel," +
                    "	vr.video_ip," +
                    "	vr.control_port," +
                    "	vr.video_name," +
                    "	COALESCE ( vr.video_pawd, '' ) video_pawd," +
                    "	r.show_type," +
                    "	r.order_num, " +
                    "	r.program_type " +
                    " FROM" +
                    "	(" +
                    "	SELECT" +
                    "		R.*," +
                    "		P.program_name " +
                    "	FROM" +
                    "		station_program_rel R," +
                    "		program_info P " +
                    "	WHERE" +
                    "		R.program_id = P.program_id " +
                    "		AND R.station_id IN ( SELECT s.station_id FROM station s WHERE s.station_level = '4' ) " +
                    "		AND R.show_type IN ( '3', '4', '5', '6' ) " +
                    "	) r" +
                    "	LEFT JOIN station_video_rec vr ON vr.ID = r.video_recorder_id " +
                    "	AND vr.station_id = r.station_id " +
                    " ORDER BY" +
                    "	r.station_id," +
                    //"	r.show_type," +
                    "	r.order_num";
                List<Dictionary<string, string>> lsDetail = ExecReader(dbConnection, sql);
                int idx = 0;
                foreach (var detail in lsDetail)
                {
                    string stationId = detail["station_id"];
                    string devType = detail["dev_type"];
                    string positionId = stationId + "_" + devType + "_1";
                    if (!HIKFourReceiveDatas.ContainsKey(positionId))
                    {
                        idx = 0;
                        HIKFourReceiveDatas.TryAdd(positionId, new());
                    }
                    HIKFourReceiveDatas[positionId].Add(detail.ToDictionary(d => d.Key.ToUpper() + "_" + idx, d => d.Value ?? ""));
                    idx++;
                }
            }
            catch (Exception e)
            {
                HIKFourReceiveDatas = null;
                CommonUtil.mLogger.LogError("获取海康威视4级收测数据异常，" + e.Message);
            }
            return HIKFourReceiveDatas;
        }

        /// <summary>
        /// 获取海康威视环境数据
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, List<Dictionary<string, string>>> GetHIKEnvData(DbConnection dbConnection)
        {
            Dictionary<string, List<Dictionary<string, string>>> HIKEnvDatas = new();
            try
            {
                //video_recorder_channel表已废弃
                //string sql = "select * from (" +
                //    " SELECT" +
                //    "	sr.station_id," +
                //    "	'0xFF01' dev_type," +
                //    "	vc.video_ip," +
                //    "	vc.control_port," +
                //    "	sr.video_name," +
                //    "	COALESCE ( sr.video_pawd, '' ) video_pawd," +
                //    "	vc.channel_no," +
                //    "	vc.channel_name " +
                //    " FROM" +
                //    "	station_video_rec sr," +
                //    "	video_recorder_channel vc " +
                //    " WHERE" +
                //    "	sr.video_ip = vc.video_ip " +
                //    "	AND sr.control_port = vc.control_port " +
                //    "	AND sr.station_id IN ( SELECT s.station_id FROM station s WHERE s.station_level IN ( '1', '2', '3' ) ) " +
                //    "	AND vc.control_port IN ( '5001', '5002' ) " +
                //    " union all" +
                //    " SELECT" +
                //    "	sr.station_id," +
                //    "	'0xFF02' dev_type," +
                //    "	vc.video_ip," +
                //    "	vc.control_port," +
                //    "	sr.video_name," +
                //    "	COALESCE ( sr.video_pawd, '' ) video_pawd," +
                //    "	vc.channel_no," +
                //    "	vc.channel_name " +
                //    " FROM" +
                //    "	station_video_rec sr," +
                //    "	video_recorder_channel vc " +
                //    " WHERE" +
                //    "	sr.video_ip = vc.video_ip " +
                //    "	AND sr.control_port = vc.control_port " +
                //    "	AND sr.station_id IN ( SELECT s.station_id FROM station s WHERE s.station_level = '4' ) " +
                //    "	AND vc.control_port IN ( '2038' ) " +
                //    " ) t" +
                //    " ORDER BY" +
                //    "	station_id," +
                //    "	dev_type," +
                //    "	control_port," +
                //    "	channel_no";

                string sql = "select * from (     " +
                    " SELECT             " +
                    "	sr.station_id,     " +
                    "	'0xFF01' dev_type, " +
                    "	sr.video_ip,       " +
                    "	sr.control_port,   " +
                    "	sr.video_name,     " +
                    "	COALESCE ( sr.video_pawd, '' ) video_pawd,          " +
                    "	vc.dictionary_value channel_no                      " +
                    " FROM                                                " +
                    "	station_video_rec sr                                " +
                    "	left join dictionary_info vc on vc.parent_id='65'   " +
                    " WHERE                                               " +
                    "	sr.station_id IN ( SELECT s.station_id FROM station s WHERE s.station_level IN ( '1', '2', '3' ) )  " +
                    "	AND sr.control_port IN ( '5001', '5002' )           " +
                    " union all                                           " +
                    " SELECT                                              " +
                    "	sr.station_id,                                      " +
                    "	'0xFF02' dev_type,                                  " +
                    "	sr.video_ip,                                        " +
                    "	sr.control_port,                                    " +
                    "	sr.video_name,                                      " +
                    "	COALESCE ( sr.video_pawd, '' ) video_pawd,          " +
                    "	vc.dictionary_value channel_no                      " +
                    " FROM                                                " +
                    "	station_video_rec sr                                " +
                    "	left join dictionary_info vc on vc.parent_id='65'   " +
                    " WHERE  " +
                    "	sr.station_id IN ( SELECT s.station_id FROM station s WHERE s.station_level = '4' )  " +
                    "	AND sr.control_port IN ( '2038' ) " +
                    " ) t           " +
                    " ORDER BY      " +
                    "	station_id,   " +
                    "	dev_type,     " +
                    "	control_port, " +
                    "	channel_no    ";
                List<Dictionary<string, string>> lsDetail = ExecReader(dbConnection, sql);
                int idx = 0;
                foreach (var detail in lsDetail)
                {
                    string stationId = detail["station_id"];
                    string devType = detail["dev_type"];
                    string positionId = stationId + "_" + devType + "_1";
                    if (!HIKEnvDatas.ContainsKey(positionId))
                    {
                        idx = 0;
                        HIKEnvDatas.TryAdd(positionId, new());
                    }
                    HIKEnvDatas[positionId].Add(detail.ToDictionary(d => d.Key.ToUpper() + "_" + idx, d => d.Value ?? ""));
                    idx++;
                }
            }
            catch (Exception e)
            {
                HIKEnvDatas = null;
                CommonUtil.mLogger.LogError("获取海康威视环境数据异常，" + e.Message);
            }
            return HIKEnvDatas;
        }

        /// <summary>
        /// 获取设备信息
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, string>> GetDeviceInfo(DbConnection dbConnection)
        {
            Dictionary<string, Dictionary<string, string>> deviceInfos = new Dictionary<string, Dictionary<string, string>>();
            try
            {
                string sql = "select * from device_info a order by position_id";
                List<Dictionary<string, string>> lsDetail = ExecReader(dbConnection, sql);
                foreach (var detail in lsDetail)
                {
                    string position_id = detail["position_id"];
                    if (!deviceInfos.ContainsKey(position_id))
                    {
                        deviceInfos.TryAdd(position_id, detail);
                    }
                }
            }
            catch (Exception e)
            {
                deviceInfos = null;
                CommonUtil.mLogger.LogError("获取设备信息异常，" + e.Message);
            }
            return deviceInfos;
        }

        /// <summary>
        /// 获取设备配置读取信息
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, string>> GetCmdSetInfo(DbConnection dbConnection)
        {
            Dictionary<string, Dictionary<string, string>> cmdSetInfo = new Dictionary<string, Dictionary<string, string>>();
            try
            {
                string sql = "select a.id, a.dev_id, a.set_id, b.device_type, b.serial_no, b.position_id, c.set_code, c.set_name, c.get_type, c.set_type, a.send_gap " +
                    " from cmd_set a, device_info b, dev_param_set c where a.dev_id = b.id and a.set_id = c.id and a.is_used = true";
                List<Dictionary<string, string>> lsDetail = ExecReader(dbConnection, sql);
                foreach (var detail in lsDetail)
                {
                    string devId = detail["dev_id"];
                    string setId = detail["set_id"];
                    string key = devId + "#" + setId;
                    if (!cmdSetInfo.ContainsKey(key))
                    {
                        cmdSetInfo.TryAdd(key, detail);
                    }
                }
            }
            catch (Exception e)
            {
                cmdSetInfo = null;
                CommonUtil.mLogger.LogError("设备配置读取信息，" + e.Message);
            }
            return cmdSetInfo;
        }

        /// <summary>
        /// 根据设置id获取最新设置
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, string> GetLatestParamSetData(DbConnection dbConnection)
        {
            Dictionary<string, string> newParamSet = new();
            try
            {
                string sql = "select a.cmd_id, a.set_text from param_data_his a where id in(select max(b.id) from param_data_his b group by b.cmd_id)";
                List<Dictionary<string, string>> lsDetail = ExecReader(dbConnection, sql);
                foreach (var detail in lsDetail)
                {
                    string cmdId = detail["cmd_id"];
                    string setText = detail["set_text"];
                    if (!newParamSet.ContainsKey(cmdId))
                    {
                        newParamSet.TryAdd(cmdId, setText);
                    }
                }
            }
            catch (Exception e)
            {
                newParamSet = null;
                CommonUtil.mLogger.LogError("最新设置异常，" + e.Message);
            }
            return newParamSet;
        }

        /// <summary>
        /// 从param_info读取配置
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, string> GetParamInfo(DbConnection dbConnection)
        {
            Dictionary<string, string> paramInfo = new();
            try
            {
                string sql = "select * from param_info a order by id";
                List<Dictionary<string, string>> lsDetail = ExecReader(dbConnection, sql);
                foreach (var detail in lsDetail)
                {
                    string parmName = detail["param_name"];
                    string parmValue = detail["param_value"];
                    if (!paramInfo.ContainsKey(parmName))
                    {
                        paramInfo.TryAdd(parmName, parmValue);
                    }
                    else
                    {
                        paramInfo[parmName] = parmValue;
                    }
                }
            }
            catch (Exception e)
            {
                paramInfo = null;
                CommonUtil.mLogger.LogError("从param_info读取配置异常，" + e.Message);
            }
            return paramInfo;
        }

        /// <summary>
        /// 获取停播时间设置
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <param name="broadcastTimeDetail">返回给前端用</param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, List<Dictionary<string, string>>>> GetBroadcastTimeSet(DbConnection dbConnection, out List<Dictionary<string, string>> broadcastTimeDetail)
        {
            Dictionary<string, Dictionary<string, List<Dictionary<string, string>>>> broadcastTimeSet = new();
            try
            {
                //string sql = "select * from station_broadcast_time_set where end_if = 0 and end_time >= getdate and del_flag = 0 order by id desc";
                string sql = "select id,station_id,COALESCE(frequency, '') frequency,begin_time,end_time,COALESCE(advance_time, 0) advance_time,station_if,COALESCE(reason_info, '') reason_info,get_time,COALESCE(oper_name, '') oper_name,COALESCE(oper_code, '') oper_code,end_if,COALESCE(memo, '') memo,title_set,COALESCE(station_name, '') station_name,COALESCE(program_name, '') program_name,del_flag from station_broadcast_time_set where end_if = 0 and end_time >= getdate and del_flag = 0 order by id desc";
                broadcastTimeDetail = ExecReader(dbConnection, sql);
                foreach (var detail in broadcastTimeDetail)
                {
                    string stationId = detail.ContainsKey("station_id") ? detail["station_id"] : "";
                    string tmpFreq = detail.ContainsKey("frequency") ? detail["frequency"] : "";
                    //台站级别的，填station
                    tmpFreq = (tmpFreq == null || "".Equals(tmpFreq)) ? "station" : tmpFreq;

                    List<string> freqs = tmpFreq.Split(",").ToList();
                    foreach (string freq in freqs)
                    { 
                        if (!broadcastTimeSet.ContainsKey(stationId))
                        {
                            broadcastTimeSet.TryAdd(stationId, new());
                        }

                        if (!broadcastTimeSet[stationId].ContainsKey(freq))
                        {
                            broadcastTimeSet[stationId].TryAdd(freq, new() { detail });
                        }
                        else
                        {
                            broadcastTimeSet[stationId][freq].Add(detail);
                        }
                    }
                }
            }
            catch (Exception e)
            {
                broadcastTimeSet = null;
                broadcastTimeDetail = null;
                CommonUtil.mLogger.LogError("从station_broadcast_time_set读取数据异常，" + e.Message);
            }
            return broadcastTimeSet;
        }

        /// <summary>
        /// 获取停播历史
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static List<Dictionary<string, string>> GetBroadcastTimeSetHis(DbConnection dbConnection)
        {
            List<Dictionary<string, string>> broadcastTimeSetHis = new();
            try
            {
                //string sql = "select * from station_broadcast_time_set where end_if = 0 and end_time >= getdate and del_flag = 0 order by id desc";
                string sql = "select id,station_id,COALESCE(frequency, '') frequency,begin_time,end_time,advance_time,station_if,COALESCE(reason_info, '') reason_info,get_time,COALESCE(oper_name, '') oper_name,COALESCE(oper_code, '') oper_code,end_if,COALESCE(memo, '') memo,title_set,COALESCE(station_name, '') station_name,COALESCE(program_name, '') program_name,del_flag from station_broadcast_time_set where end_if = 1 and del_flag = 0 order by id desc";
                broadcastTimeSetHis = ExecReader(dbConnection, sql);
            }
            catch (Exception e)
            {
                CommonUtil.mLogger.LogError("从station_broadcast_time_set读取历史停播数据异常，" + e.Message);
            }
            return broadcastTimeSetHis;
        }

        /// <summary>
        /// 从stat_column_info\stat_column_detail读取配置
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Object> GetStatInfo(DbConnection dbConnection)
        {
            Dictionary<string, Object> statDetails = new();

            try
            {
                //主表
                Dictionary<string, Dictionary<string, string>> statMain = new();
                //台站级别关联ID
                Dictionary<string, Dictionary<string, string>> statLevel = new();
                Dictionary<string, Dictionary<string, Dictionary<string, string>>> statAlarmIds = new();
                //先得到明细
                Dictionary<string, List<Dictionary<string, string>>> statDetail = new();

                string sql = "select * from stat_column_detail a where exists(select 1 from stat_column_info b where b.id = a.stat_id and b.used = '1') order by stat_id,id";
                List<Dictionary<string, string>> lsDetail = ExecReader(dbConnection, sql);
                foreach (var detail in lsDetail)
                {
                    string statId = detail["stat_id"];
                    string colName = detail["col_name"];
                    string alarmIds = detail.ContainsKey("alarm_ids") ? detail["alarm_ids"] : "";
                    if (!statDetail.ContainsKey(statId))
                    {
                        statDetail.TryAdd(statId, new());
                    }
                    if (alarmIds != null && !"".Equals(alarmIds))
                    {
                        if (!statAlarmIds.ContainsKey(statId))
                        { 
                            statAlarmIds.TryAdd(statId, new());
                        }
                        if (!statAlarmIds[statId].ContainsKey(colName))
                        {
                            statAlarmIds[statId].Add(colName, new());
                        }
                        foreach (string alarmId in alarmIds.Split(","))
                        {
                            if (!statAlarmIds[statId][colName].ContainsKey(alarmId))
                            {
                                statAlarmIds[statId][colName].Add(alarmId, "");
                            }
                        }
                    }

                    statDetail[statId].Add(detail);
                }
                statDetails.TryAdd("statDetail", statDetail);
                statDetails.TryAdd("statAlarmIds", statAlarmIds);

                //查询主表
                sql = "select * from stat_column_info a where a.used = '1' order by id";
                List<Dictionary<string, string>> lsMain = ExecReader(dbConnection, sql);
                foreach (var detail in lsMain)
                {
                    string id = detail["id"];
                    string stationLevels = detail["station_levels"];

                    if (!statMain.ContainsKey(id))
                    {
                        statMain.TryAdd(id, detail);
                    }

                    foreach (string stationLevel in stationLevels.Split(','))
                    {
                        if (!statLevel.ContainsKey(stationLevel))
                        {
                            statLevel.TryAdd(stationLevel, new());
                        }

                        statLevel[stationLevel].TryAdd(id, "");
                    }
                }
                statDetails.TryAdd("statMain", statMain);
                statDetails.TryAdd("statLevelToMain", statLevel);
            }
            catch (Exception e)
            {
                statDetails = null;
                CommonUtil.mLogger.LogError("从stat_column_info读取配置异常，" + e.Message);
            }
            return statDetails;
        }

        /// <summary>
        /// 定时测试任务信息
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, string>> GetTaskInfo(DbConnection dbConnection, string where = "")
        {
            Dictionary<string, Dictionary<string, string>> taskInfo = new();
            try
            {
                string sql = "select * from task_test_info a where 1=1 " + where + " order by id";
                List<Dictionary<string, string>> lsDetail = ExecReader(dbConnection, sql);
                foreach (var detail in lsDetail)
                {
                    string taskId = detail["id"];
                    if (!taskInfo.ContainsKey(taskId))
                    {
                        taskInfo.TryAdd(taskId, detail);
                    }
                    else
                    {
                        taskInfo[taskId] = detail;
                    }
                }
            }
            catch (Exception e)
            {
                taskInfo = null;
                CommonUtil.mLogger.LogError("从task_test_info读取任务异常，" + e.Message);
            }
            return taskInfo;
        }

        /// <summary>
        /// 告警提示初始化信息
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, Dictionary<string, Dictionary<string, string>>>> GetAlarmTipInfo(DbConnection dbConnection)
        {
            Dictionary<string, Dictionary<string, Dictionary<string, Dictionary<string, string>>>> alarmSets = new();
            try
            {
                string sql = "select station_level, alarm_level, tip_level, tip_minute, time_out from alarm_tip_set where used = '1' order by station_level, alarm_level, tip_level";
                List<Dictionary<string, string>> lsDetail = ExecReader(dbConnection, sql);
                foreach (var detail in lsDetail)
                {
                    string station_level = detail["station_level"];
                    string alarm_level = detail["alarm_level"];
                    string tip_level = detail["tip_level"];

                    if (!alarmSets.ContainsKey(station_level))
                    {
                        alarmSets.TryAdd(station_level, new());
                    }
                    if (!alarmSets[station_level].ContainsKey(alarm_level))
                    {
                        alarmSets[station_level].TryAdd(alarm_level, new());
                    }
                    if (!alarmSets[station_level][alarm_level].ContainsKey(tip_level))
                    {
                        alarmSets[station_level][alarm_level].TryAdd(tip_level, detail.Where(d => "tip_minute".Equals(d.Key) || "time_out".Equals(d.Key)).ToDictionary(d => d.Key, d => d.Value));
                    }
                }
            }
            catch (Exception e)
            {
                alarmSets = null;
                CommonUtil.mLogger.LogError("从alarm_tip_set读取数据异常，" + e.Message);
            }
            return alarmSets;
        }

        /// <summary>
        /// 静态数据转动态数据
        /// 从视图v_static_change_dynamic读取
        /// 格式固定 station_id，dev_type，pno，datakey，dataval
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static List<Dictionary<string, string>> GetFormatStaticData(DbConnection dbConnection)
        {
            List<Dictionary<string, string>> staticDatas;
            try
            {
                string sql = "select * from v_static_change_dynamic";
                staticDatas = ExecReader(dbConnection, sql);
            }
            catch (Exception e)
            {
                staticDatas = null;
                CommonUtil.mLogger.LogError("从v_static_change_dynamic读取任务异常，" + e.Message);
            }
            return staticDatas;
        }

        /// <summary>
        /// 调频、中波提前开机或延迟关机分钟数
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, Dictionary<string, string>>> GetTimeAdvanceAndDelayInfo(DbConnection dbConnection)
        {
            Dictionary<string, Dictionary<string, Dictionary<string, string>>> timeAdvanceAndDelayInfo = new();
            try
            {
                string sql = "select * from time_advance_delay a";
                List<Dictionary<string, string>> lsDetail = ExecReader(dbConnection, sql);
                foreach (var detail in lsDetail)
                {
                    string stationId = detail["station_id"];
                    string freq = detail["frequency"];
                    if (!timeAdvanceAndDelayInfo.ContainsKey(stationId))
                    {
                        timeAdvanceAndDelayInfo.TryAdd(stationId, new());
                    }
                    if (!timeAdvanceAndDelayInfo[stationId].ContainsKey(freq))
                    {
                        timeAdvanceAndDelayInfo[stationId].TryAdd(freq, detail);
                    }
                }
            }
            catch (Exception e)
            {
                timeAdvanceAndDelayInfo = null;
                CommonUtil.mLogger.LogError("从time_advance_delay读取任务异常，" + e.Message);
            }
            return timeAdvanceAndDelayInfo;
        }

        /// <summary>
        /// 通过信通系统派单台站匹配信息
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static List<Dictionary<string, string>> GetStationIdMatchInfo(DbConnection dbConnection)
        {
            List<Dictionary<string, string>> stationIdMatchInfos = null;
            try
            {
                string sql = "select distinct station_id, COALESCE(xt_intid, '') xt_intid from xtstation_relation a order by station_id";
                stationIdMatchInfos = ExecReader(dbConnection, sql);
            }
            catch (Exception e)
            {
                stationIdMatchInfos = null;
                CommonUtil.mLogger.LogError("从xtstation_relation读取配置异常，" + e.Message);
            }
            return stationIdMatchInfos;
        }

        /// <summary>
        /// 告警基础表
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static List<Dictionary<string, string>> GetAlarmBasicInfo(DbConnection dbConnection)
        {
            List<Dictionary<string, string>> alarmBasicInfos = null;
            try
            {
                string sql = "select * from alarm_basic_info a order by alarm_id";
                alarmBasicInfos = ExecReader(dbConnection, sql);
            }
            catch (Exception e)
            {
                alarmBasicInfos = null;
                CommonUtil.mLogger.LogError("从alarm_basic_info读取配置异常，" + e.Message);
            }
            return alarmBasicInfos;
        }

        /// <summary>
        /// 台站联系人
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, List<Dictionary<string, string>>> GetStationManager(DbConnection dbConnection)
        {
            Dictionary<string, List<Dictionary<string, string>>> stationManager = null;
            string authDbName = "USERMANAGE2";

            //Dictionary<string, string> subLeader = CommonUtil.GetSubLeader();
            //Dictionary<string, string> deptOper = CommonUtil.GetDeptOper();

            try
            {
                string sql = "select a.RealName, COALESCE(a.PhoneNumber, '') PhoneNumber, b.ClaimValue SRole, COALESCE(r.SRoleName, '') SRoleName, c.ClaimValue BranchUnit, m.BranchUnitName, d.ClaimValue MainTainDept, COALESCE(n.MainTainDeptName, '') MainTainDeptName " +
                    " from " + authDbName + ".AspNetUsers a " +
                    " left join " + authDbName + ".AspNetUserClaims b on a.Id = b.UserId and b.ClaimType = 'SRole' " +
                    " left join " + authDbName + ".AspNetUserClaims c on a.Id = c.UserId and c.ClaimType = 'BranchUnit' " +
                    " left join " + authDbName + ".AspNetUserClaims d on a.Id = d.UserId and d.ClaimType = 'MainTainDept' " +
                    " left join " + authDbName + ".SRole r on b.ClaimValue like '%\"'||r.SRoleId||'\"%' " +
                    " left join " + authDbName + ".BranchUnit m on c.ClaimValue = m.BranchUnitId " +
                    " left join " + authDbName + ".MainTainDept n on d.ClaimValue like '%[\"'||n.MainTainDeptId||'\"%' " +
                    " where b.ClaimValue is not null and a.IsUse = 1 " +
                    //" order by c.ClaimValue, r.SRoleId, d.ClaimValue";
                    //10017是维护部主任，维护部排前面，领导排后面
                    //维护部是主任在前，其它的是副职在前
                    " order by c.ClaimValue, case when r.SRoleId = 10017 then r.SRoleId + 100 when r.SRoleId = 10018 then r.SRoleId + 98 else r.SRoleId end desc, d.ClaimValue";
                
                List<Dictionary<string, string>> lsDetails = ExecReader(dbConnection, sql);
                stationManager = new();
                foreach (var detail in lsDetails)
                {
                    string BranchUnit = detail.ContainsKey("BranchUnit") ? detail["BranchUnit"] : "";
                    string MainTainDept = detail.ContainsKey("MainTainDept") ? detail["MainTainDept"] : "";
                    string SRole = detail.ContainsKey("SRole") ? detail["SRole"] : "";
                    SRole = SRole.Replace("[", "").Replace("]", "").Replace("\"", "");

                    if (BranchUnit != null && !"".Equals(BranchUnit) && !"NULL".Equals(BranchUnit))
                    {
                        if (!stationManager.ContainsKey(BranchUnit))
                        {
                            stationManager.TryAdd(BranchUnit, new());
                        }

                        //分中心的，存储在一起
                        stationManager[BranchUnit].Add(detail);
                    }
                    
                    if (MainTainDept != null && !"".Equals(MainTainDept) && !"NULL".Equals(MainTainDept))
                    {
                        List<string> deptIds = JsonConvert.DeserializeObject<List<string>>(MainTainDept);
                        foreach (string deptId in deptIds)
                        {
                            if (!stationManager.ContainsKey(deptId))
                            {
                                stationManager.TryAdd(deptId, new());
                            }
                            //维护部的，存在一起，可能和分中心节点的重复
                            stationManager[deptId].Add(detail);
                        }
                    }
                }
            }
            catch (Exception e)
            {
                stationManager = null;
                CommonUtil.mLogger.LogError("从USERMANAGE汇总联系人信息异常，" + e.Message);
            }
            return stationManager;
        }

        /// <summary>
        /// 台站联系人
        /// 密改
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, List<Dictionary<string, string>>> GetMgStationManager(DbConnection dbConnection)
        {
            Dictionary<string, List<Dictionary<string, string>>> stationManager = null;
            string authDbName = "USERMANAGE_S";

            //Dictionary<string, string> subLeader = CommonUtil.GetSubLeader();
            //Dictionary<string, string> deptOper = CommonUtil.GetDeptOper();

            try
            {
                string sql = "select a.RealName, COALESCE(a.PhoneNumber, '') PhoneNumber, b.ClaimValue SRole, COALESCE(r.SRoleName, '') SRoleName, c.ClaimValue BranchUnit, m.BranchUnitName, d.ClaimValue MainTainDept, COALESCE(n.MainTainDeptName, '') MainTainDeptName " +
                    " from " + authDbName + ".AspNetUsers a " +
                    " left join " + authDbName + ".AspNetUserClaims b on a.Id = b.UserId and b.ClaimType = 'SRole' " +
                    " left join " + authDbName + ".AspNetUserClaims c on a.Id = c.UserId and c.ClaimType = 'BranchUnit' " +
                    " left join " + authDbName + ".AspNetUserClaims d on a.Id = d.UserId and d.ClaimType = 'MainTainDept' " +
                    " left join " + authDbName + ".SRole r on b.ClaimValue like '%\"'||r.SRoleId||'\"%' " +
                    " left join " + authDbName + ".BranchUnit m on c.ClaimValue = m.BranchUnitId " +
                    " left join " + authDbName + ".MainTainDept n on d.ClaimValue like '%[\"'||n.MainTainDeptId||'\"%' " +
                    " where b.ClaimValue is not null and a.IsUse = 1 " +
                    " order by c.ClaimValue, r.SRoleId, d.ClaimValue";
                List<Dictionary<string, string>> lsDetails = ExecReader(dbConnection, sql);
                stationManager = new();
                foreach (var detail in lsDetails)
                {
                    string BranchUnit = detail.ContainsKey("BranchUnit") ? detail["BranchUnit"] : "";
                    string MainTainDept = detail.ContainsKey("MainTainDept") ? detail["MainTainDept"] : "";
                    string SRole = detail.ContainsKey("SRole") ? detail["SRole"] : "";
                    SRole = SRole.Replace("[", "").Replace("]", "").Replace("\"", "");

                    if (BranchUnit != null && !"".Equals(BranchUnit) && !"NULL".Equals(BranchUnit))
                    {
                        if (!stationManager.ContainsKey(BranchUnit))
                        {
                            stationManager.TryAdd(BranchUnit, new());
                        }

                        //分中心的，存储在一起
                        stationManager[BranchUnit].Add(detail);
                    }

                    if (MainTainDept != null && !"".Equals(MainTainDept) && !"NULL".Equals(MainTainDept))
                    {
                        List<string> deptIds = JsonConvert.DeserializeObject<List<string>>(MainTainDept);
                        foreach (string deptId in deptIds)
                        {
                            if (!stationManager.ContainsKey(deptId))
                            {
                                stationManager.TryAdd(deptId, new());
                            }
                            //维护部的，存在一起，可能和分中心节点的重复
                            stationManager[deptId].Add(detail);
                        }
                    }
                }
            }
            catch (Exception e)
            {
                stationManager = null;
                CommonUtil.mLogger.LogError("从USERMANAGE汇总联系人信息异常，" + e.Message);
            }
            return stationManager;
        }

        /// <summary>
        /// 告警跳变规则数据
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, AlarmRules> GetAlarmRuleInfos(DbConnection dbConnection)
        {
            Dictionary<string, AlarmRules> alarmRuleInfos = new();
            try
            {
                string sql = "select * from alarm_basic_rules a order by id";
                List<Dictionary<string, string>> ruleDetails = ExecReader(dbConnection, sql);
                foreach (var detail in ruleDetails)
                {
                    string ruleId = detail["id"];

                    bool t2 = "1".Equals(detail["change_two_is_enabled"]);
                    bool t1 = "1".Equals(detail["change_one_is_enabled"]);
                    int t2t = int.Parse(detail["change_two_time"]);
                    int t1t = int.Parse(detail["change_one_time"]);

                    if (!alarmRuleInfos.ContainsKey(ruleId))
                    {
                        alarmRuleInfos.TryAdd(ruleId, new() { ruleId = ruleId, t2 = t2, t2t = t2t, t1 = t1, t1t = t1t });
                    }
                }
            }
            catch (Exception e)
            {
                alarmRuleInfos = null;
                CommonUtil.mLogger.LogError("从alarm_basic_rules读取配置异常，" + e.Message);
            }
            return alarmRuleInfos;
        }

        /// <summary>
        /// 从服务器获取1.0监控1~3级台站调频、中波的当前在用时间表
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, Dictionary<string, List<TimePeriod>>>> GetOldSystemUseTimesTable(DbConnection dbConnection)
        {
            Dictionary<string, Dictionary<string, Dictionary<string, List<TimePeriod>>>> systemUseTimesTables = new();
            Dictionary<string, Dictionary<string, Dictionary<string, List<TimePeriod>>>> systemUseTimesTablesNew = new();
            try
            {
                string sql = "select * from GJ_t_BrocastTime a where a.节目类型 in('FM','MW') and 当前使用 = '1' order by BranchCode, 节目名称, 节目频率";
#if DEBUG
                //sql = "select * from GJ_t_BrocastTime a where a.节目类型 in('FM','MW') and 当前使用 = '1' and BranchCode = '1116' and 节目频率 = '88.3' order by BranchCode, 节目名称, 节目频率";
                //sql = "select * from GJ_t_BrocastTime a where a.节目类型 in('FM','MW') and 当前使用 = '1' and BranchCode = '0507' and 节目频率 = '1206' order by BranchCode, 节目名称, 节目频率";
#endif
                List<Dictionary<string, string>> timesDetails = ExecReader(dbConnection, sql);
                foreach (var detail in timesDetails)
                {
                    string stationId = detail["BranchCode"];
                    string freq = detail["节目频率"];
                    if (!systemUseTimesTables.ContainsKey(stationId))
                    {
                        systemUseTimesTables.TryAdd(stationId, new());
                    }
                    if (!systemUseTimesTables[stationId].ContainsKey(freq))
                    {
                        systemUseTimesTables[stationId].TryAdd(freq, new());
                    }

                    //根据数据库表结构特征，这样循环读取
                    for (int i = 0; i < 7; i++)
                    {
                        TimePeriod[] timePeriods = new TimePeriod[2] { new TimePeriod(), new TimePeriod() };
                        timePeriods[0].timeStart = detail["星期" + i + "开1"].ToString();
                        timePeriods[1].timeStart = detail["星期" + i + "开2"].ToString();
                        timePeriods[0].timeEnd = detail["星期" + i + "关1"].ToString();
                        timePeriods[1].timeEnd = detail["星期" + i + "关2"].ToString();

                        if (!systemUseTimesTables[stationId][freq].ContainsKey(i.ToString()))
                        {
                            //两种方式目前看来是一样的
                            List<TimePeriod> formatTimePeriods = SetTimesTableFormatNew(timePeriods);
                            systemUseTimesTables[stationId][freq].TryAdd(i.ToString(), formatTimePeriods);
                        }
                    }

                    if (!systemUseTimesTablesNew.ContainsKey(stationId))
                    {
                        systemUseTimesTablesNew.TryAdd(stationId, new());
                    }
                    if (!systemUseTimesTablesNew[stationId].ContainsKey(freq))
                    {
                        systemUseTimesTablesNew[stationId].TryAdd(freq, new());
                    }

                    /*********************新的判断逻辑********************************************************/
                    //根据数据库表结构特征，这样循环读取
                    int wno = 0;
                    for (int i = 0; i < 7; i++)
                    {
                        if (!systemUseTimesTablesNew[stationId][freq].ContainsKey(wno.ToString()))
                        { 
                            systemUseTimesTablesNew[stationId][freq].TryAdd(wno.ToString(), new());
                        }

                        TimePeriod[] timePeriods = new TimePeriod[2] { new TimePeriod(), new TimePeriod() };
                        timePeriods[0].timeStart = detail["星期" + i + "开1"].ToString();
                        timePeriods[1].timeStart = detail["星期" + i + "开2"].ToString();
                        timePeriods[0].timeEnd = detail["星期" + i + "关1"].ToString();
                        timePeriods[1].timeEnd = detail["星期" + i + "关2"].ToString();

                        bool add = false;
                        foreach (TimePeriod tp in timePeriods)
                        {
                            if (tp.iStart < tp.iEnd)
                            {
                                SetTimePeriod(systemUseTimesTablesNew[stationId][freq][wno.ToString()], tp);
                            }
                            else if (tp.iStart == tp.iEnd)
                            {
                                if (systemUseTimesTablesNew[stationId][freq][wno.ToString()].Count == 0)
                                {
                                    SetTimePeriod(systemUseTimesTablesNew[stationId][freq][wno.ToString()], new TimePeriod() { timeStart = "00:00:00", timeEnd = "23:59:59" });
                                }
                            }
                            else
                            {
                                SetTimePeriod(systemUseTimesTablesNew[stationId][freq][wno.ToString()], new TimePeriod() { timeStart = tp.timeStart, timeEnd = "23:59:59" });
                                if (tp.iEnd == 0)
                                {
                                    break;
                                }

                                wno++;
                                bool loop = false;
                                if (wno >= 7)
                                {
                                    loop = true;
                                    wno = 0;
                                }
                                add = true;

                                if (!systemUseTimesTablesNew[stationId][freq].ContainsKey(wno.ToString()))
                                {
                                    systemUseTimesTablesNew[stationId][freq].TryAdd(wno.ToString(), new());
                                }
                                
                                SetTimePeriod(systemUseTimesTablesNew[stationId][freq][wno.ToString()], new TimePeriod() { timeStart = "00:00:00", timeEnd = tp.timeEnd }, loop);
                                break;
                            }
                        }

                        if (!add)
                        {
                            wno++;
                            if (wno >= 7)
                            {
                                wno = 0;
                            }
                            if (!systemUseTimesTablesNew[stationId][freq].ContainsKey(wno.ToString()))
                            {
                                systemUseTimesTablesNew[stationId][freq].TryAdd(wno.ToString(), new());
                            }
                        }
                    }
                    /*********************新的判断逻辑********************************************************/
                }
            }
            catch (Exception e)
            {
                systemUseTimesTables = null;
                systemUseTimesTablesNew = null;
                CommonUtil.mLogger.LogError("从GJ_t_BrocastTime读取配置异常，" + e.Message);
            }
            //return systemUseTimesTables;
            return systemUseTimesTablesNew;
        }

        /// <summary>
        /// 新的时间表逻辑
        /// </summary>
        /// <param name="timePeriods"></param>
        /// <param name="tpset"></param>
        /// <param name="loop"></param>
        public static void SetTimePeriod(List<TimePeriod> timePeriods, TimePeriod tpset, bool loop = false)
        {
            bool match = false;
            for (int i = 0; i < timePeriods.Count - 1; i++)
            {
                var tpTmp = timePeriods[i];
                if (tpTmp.iStart == tpset.iStart)
                {
                    if (tpTmp.iEnd < tpset.iEnd)
                    {
                        timePeriods[i].timeEnd = TransForm.DateTimeStrFormat(tpset.timeEnd, "HH:mm:ss");
                    }
                    match = true;
                }
                else if (tpTmp.iEnd == tpset.iEnd)
                {
                    if (tpTmp.iStart > tpset.iStart)
                    {
                        timePeriods[i].timeStart = TransForm.DateTimeStrFormat(tpset.timeStart, "HH:mm:ss");
                    }
                    match = true;
                }
            }

            if (!match)
            {
                if (loop)
                {
                    timePeriods.Add(new());
                    for (int i = timePeriods.Count - 1; i > 0; i--)
                    {
                        timePeriods[i].timeStart = timePeriods[i - 1].timeStart;
                        timePeriods[i].timeEnd = timePeriods[i - 1].timeEnd;
                    }
                    timePeriods[0].timeStart = TransForm.DateTimeStrFormat(tpset.timeStart, "HH:mm:ss");
                    timePeriods[0].timeEnd = TransForm.DateTimeStrFormat(tpset.timeEnd, "HH:mm:ss");
                }
                else
                {
                    timePeriods.Add(new());
                    timePeriods.Last().timeStart = TransForm.DateTimeStrFormat(tpset.timeStart, "HH:mm:ss");
                    timePeriods.Last().timeEnd = TransForm.DateTimeStrFormat(tpset.timeEnd, "HH:mm:ss");
                }
            }
        }

        /// <summary>
        /// 时间表逻辑
        /// </summary>
        /// <param name="timePeriods"></param>
        /// <returns></returns>
        public static List<TimePeriod> SetTimesTableFormatNew(TimePeriod[] timePeriods)
        {
            //标准格式时间表，初始默认一段
            Dictionary<string, bool> tmpTimePeriods = new() { { "00:00:00", true }, { "23:59:59", false } };
            List<TimePeriod> formatTimePeriods = new List<TimePeriod>();
            try
            {
                foreach (TimePeriod timePeriod in timePeriods)
                {
                    string start = TransForm.DateTimeStrFormat(timePeriod.timeStart, "HH:mm:ss");
                    string end = TransForm.DateTimeStrFormat(timePeriod.timeEnd, "HH:mm:ss");
                    if (!tmpTimePeriods.ContainsKey(start))
                    {
                        tmpTimePeriods.TryAdd(start, true);
                    }
                    if (!tmpTimePeriods.ContainsKey(end))
                    {
                        tmpTimePeriods.TryAdd(end, false);
                    }
                }
                tmpTimePeriods = tmpTimePeriods.OrderBy(d => d.Key).ToDictionary(d => d.Key, d => d.Value);

                int idx = -1;
                bool current = true;
                foreach (string key in tmpTimePeriods.Keys)
                {
                    if (tmpTimePeriods[key] == current)
                    {
                        if (tmpTimePeriods[key])
                        {
                            idx++;
                            formatTimePeriods.Add(new() { timeStart = key });
                        }
                        else
                        {
                            formatTimePeriods[idx].timeEnd = key;
                        }

                        current = !current;
                    }
                    else
                    {
                        if (tmpTimePeriods[key])
                        {
                            formatTimePeriods[idx].timeStart = key;
                        }
                        else if (!"23:59:59".Equals(key))
                        {
                            formatTimePeriods[idx].timeEnd = key;
                        }
                    }
                }
            }
            catch (Exception e)
            {
                formatTimePeriods = new List<TimePeriod>() { new() };
                CommonUtil.mLogger.LogError("解析时间表异常，" + e.Message);
            }

            return formatTimePeriods;
        }
        #endregion

        #region 历史数据存储
        /// <summary>
        /// 历史存储设置
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, string>> GetHisDataInfo(DbConnection dbConnection)
        {
            Dictionary<string, Dictionary<string, string>> hisSetMainInfos = new();
            try
            {
                string sql = "select a.position_id, a.dev_type, a.used, a.save_type, a.save_count from his_set_main a order by dev_type, position_id";
                List<Dictionary<string, string>> lsDetails = ExecReader(dbConnection, sql);
                foreach (var detail in lsDetails)
                {
                    string position_id = detail["position_id"];
                    if(!hisSetMainInfos.ContainsKey(position_id))
                    {
                        hisSetMainInfos.TryAdd(position_id, detail);
                    }
                }
            }
            catch (Exception e)
            {
                hisSetMainInfos = null;
                CommonUtil.mLogger.LogError("从his_set_main读取配置异常，" + e.Message);
            }
            return hisSetMainInfos;
        }

        /// <summary>
        /// 历史存储设置明细
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns></returns>
        public static Dictionary<string, List<Dictionary<string, string>>> GetHisDetailInfo(DbConnection dbConnection)
        {
            Dictionary<string, List<Dictionary<string, string>>> hisDetailInfo = new();
            try
            {
                string sql = "select a.dev_type, a.col_name, a.col_type, a.col_len, a.para_no, a.col_desc, a.index_if from his_set_sub a order by dev_type, id";
                List<Dictionary<string, string>> lsDetails = ExecReader(dbConnection, sql);
                foreach (var detail in lsDetails)
                {
                    string dev_type = detail["dev_type"];
                    if (!hisDetailInfo.ContainsKey(dev_type))
                    {
                        hisDetailInfo.TryAdd(dev_type, new());
                    }

                    hisDetailInfo[dev_type].Add(detail);
                }
            }
            catch (Exception e)
            {
                hisDetailInfo = null;
                CommonUtil.mLogger.LogError("从his_set_sub读取配置异常，" + e.Message);
            }
            return hisDetailInfo;
        }
        #endregion
    }
}
